Search This Blog

Thursday, June 10, 2010

Script to check Blocked processes with relevant detail

Here is the script to check the blocked processes with detail;

Declare @vTable table(SPID int,[Status] nvarchar(256),
Login nvarchar(256),HostName nvarchar(256),
BlkBy nvarchar(256),DBName nvarchar(256),
Command nvarchar(max),CPUTime int
,DiskIO int,LastBatch nvarchar(256),
ProgramName nvarchar(256),SPID1 int,

Insert into @vTable
Exec sp_who2

Select SPID,Status,HostName,BlkBy as BlockBy, DBName, Command, CPUTime,DiskIO, ProgramName,objectid,text
from (Select a.sql_handle,b.* from sys.dm_exec_requests a
Inner Join @vTable b on b.SPID = a.session_ID) Main
cross apply sys.dm_exec_sql_text (Main.sql_handle)
where BlkBy <> ' .'

No comments:

Post a Comment