http://www.sqlservercentral.com/blogs/sql-server-citation-sql-blog-by-hemantgiri-s-goswami-sql-mvp/2012/01/30/when-dbcc-inputbuffer-disappoints/

Printed 2014/07/24 07:51AM

When DBCC INPUTBUFFER disappoints

2012/01/30

Today we have an alert from one of the client server about blocking, I have immediately start looking at it using below statement

 
SELECT * FROM
MASTER..SYSPROCESSES
WHERE BLOCKED != 0 
Yes, I’ve found the culprit SPID that is occupying more resources, using DBCC INPUTBUFFER(SPID). so, instead of seeing what exactly this SPID is doing I have received SP_EXECUTESQL, surprised!!! No, it’s bound to happen when SPID is running dynamic TSQL (using SP_EXECUTESQL) and/or cursor. But, yes, at the same time I would like to know what it is running behind the scene. There are two options that came to my mind
I preferred to use 2nd option as this is one time effort (at least as of now), and, it would be very quick. So, here is what I have used
 

-- Variable that will store the SQLHandle
DECLARE @SQLHandle BINARY(20)

-- Variable that will pass on the culprit SPID
DECLARE @SPID INT

-- value for culprit SPID
SET @SPID = 52

-- this will give you the SQLHandle for the culprit SPID
SELECT @SQLHandle = SQL_HANDLE
FROM MASTER..SYSPROCESSES
WHERE SPID = @SPID

-- this statement will give you the SQL Statement for culprit SPID
SELECT [TEXT] FROM ::FN_GET_SQL(@SQLHandle)
You must be thinking why I have used this function instead sys.dm_exec_sql_text? Any guess??
Yes, you are right, customer is still using SQL server 2000 {Winking smile}
BTW, if you happened to come across something relating but on SQL Server 2005 or SQL Server 2008 and greater I have a reference script for you

Erland Sommarskog, SQL Server MVP has written aba_lockinfo and a script a.k.a. Custom Blocker Report from Aaron_Bertrand.
 
--Hemantgiri S. Goswami (http://www.sql-server-citation.com )

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.