Feeds:
Posts
Comments

Posts Tagged ‘Deadlock’

1.Check for all running process

 

USE
Master

GO

 

SELECT
SPID,DBID FROM SYSPROCESSES

WHERE
DBID NOT IN (1,2,3,4)

/* Master-1, Tempdb-2, Mode-3, MSDB-4*/

 

AND
SPID >50

/*SQL Server reserves SPID values of 1 to 50 for internal use*/

 

AND
SPID<> @@spid         

/*To exclude current user process*/

 

In my case above Query returns

SPID  DBID

54       16

 

2.To find out which database associated with above dbid

SELECT

NAME
FROM

SYSDATABASES
WHERE DBID=16

 

3.To find out Query associated with above spid

 

DECLARE

@handle
VARBINARY(64)

SELECT

@handle
= sql_handle

FROM
sys.sysprocesses

WHERE
SPID =54

SELECT
text

FROM
sys.dm_exec_sql_text(@handle)

 

4.To kill this process

/*KILL SPID*/

KILL
54

GO

 

You can kill all other running process by using KILL command
like above

 

 

Read Full Post »

%d bloggers like this: