Find blocking query details from sysprocesses, including text, host, time, etc

Here is a query that I wrote to find details of the blocked queries :

SELECT

— Get Blocked details :

‘spid ‘ + cast(A.spid as varchar(100))       + ‘ is being blocked by spid ‘ + cast(A.blocked as varchar(100)) as Blocked_SPIDs,

A.loginame as Blocked_Loginame, A.open_tran as Blocked_Open_transaction, T.text as BlockedText,       DB_NAME(A.dbid) as Blocked_DatabaseName,

A.program_name as Blocked_Program_name, A.nt_domain as Blocked_NT_Domain, A.nt_username as Blocked_OS_Username, A.login_time as Blocked_LoginTime,

A.status as Blocked_Status, A.waittime as Blocked_Time_In_Milliseconds, SUBSTRING(A.hostname,1, 12) as Blocked_Host,

— Get Blocking details :

B.loginame as Blocker_Loginame, B.open_tran as Blocker_Open_transaction, R.text as BlockerText,       DB_NAME(B.dbid) as Blocker_DatabaseName,

B.program_name as Blocker_Program_name, B.nt_domain as Blocker_NT_Domain, B.nt_username as Blocker_OS_Username, B.login_time as Blocker_LoginTime,

B.status as Blocker_Status, B.waittime as Blocker_Time_In_Milliseconds, SUBSTRING(B.hostname,1, 12) as Blocker_Host

FROM sys.sysprocesses A

OUTER APPLY sys.dm_exec_sql_text(sql_handle) T — this is the blocked text

INNER JOIN sys.sysprocesses B

OUTER APPLY sys.dm_exec_sql_text(sql_handle) R — this is the blocker text

ON B.spid = A.blocked

and A.spid>50

and A.blocked ! = 0 — Identifies any blocking spids

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s