Get blocking query

If you want to capture the SPID and the query that is causing blocking then refer to my previous post
http://saveadba.blogspot.com/2012/02/find-query-causing-blocking-extended.html


But I have  come across situations where I am troubleshooting for extended blocking. But unfortunately the queries causing the extended blocking finish execution before I can login to the server. So I have written a script which will capture all the information that I need. I save the output to a file via a job and I email the output to myself. This way I have a record of the queries that cause extended blocking.

I have set up the following script in a job and have configured the job to save the output to a file. Following is a sample of the output which tells me which query is causing blocking and which one is being blocked.


Query #1
run by spid : 62
This is the SPID that is causing the blocking
 hostname                            loginame
--------
SQLServerprd01          Appuser


--------
 Running DBCC INPUTBUFFER for SPID 62
EventType Parameters EventInfo
--------- ---------- ---------
Language Event 0  insert into test...........

Use the following script for this.............................................................................................

use master

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
set nocount on
DECLARE @RefCount varchar(100)
DECLARE @sql1 VARCHAR(4000),@sql2 VARCHAR(4000)
DECLARE @querynum varchar(100)
DECLARE @sqlcmd varchar(8000)
DECLARE @request_status varchar(50)
SET @querynum=1


DECLARE RefCursor CURSOR FOR


SELECT
distinct(l.request_SESSION_Id)
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND
( l.resource_description = l1.resource_description OR (l.resource_description IS NULL AND l1.resource_description IS NULL))


OPEN RefCursor
FETCH NEXT FROM RefCursor INTO @RefCount
WHILE @@FETCH_STATUS = 0
BEGIN
Select 'Query #'+@querynum+'
run by spid : '+@RefCount
SET @request_status=(SELECT
distinct(l.request_status)
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND ( l.resource_description = l1.resource_description OR
(l.resource_description IS NULL AND l1.resource_description IS NULL))
and l.request_session_id=@RefCount)
IF (@request_status='WAIT')
begin
     PRINT 'This is the SPID that is being blocked'
    end
else
begin
     PRINT 'This is the SPID that is causing the blocking'
     end
select ''
SET @sqlcmd='Select hostname from sysprocesses where spid='+@RefCount
EXEC (@sqlcmd)


SET @sqlcmd='Select loginame from sysprocesses where spid='+@RefCount
EXEC (@sqlcmd)
SET @querynum=@querynum+1
SET @sql2='DBCC INPUTBUFFER('+@RefCount+') with no_infomsgs'
SELECT 'Running DBCC INPUTBUFFER for SPID '+@RefCount
EXEC (@sql2)



SELECT '-----------------------------------------------------------------'


FETCH NEXT FROM RefCursor INTO @RefCount
END
CLOSE RefCursor
DEALLOCATE RefCursor
GO