When DBCC INPUTBUFFER disappoints

Posted in: Technical Track

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


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

  • Using Profiler
  • Using function – ::fn_get_sql(@SQLHandle)

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
— Variable that will pass on the culprit SPID
— value for culprit SPID
SET @SPID = 52
— this will give you the SQLHandle for the culprit SPID
— this statement will give you the SQL Statement for culprit SPID
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}
TIP: If you happened to face similar situation but on SQL Server 2005 or SQL Server 2008 and greater, there are scripts that you can use :

Erland Sommarskog, SQL Server MVP has written aba_lockinfo and a script a.k.a. Custom Blocker Report from Aaron Bertrand.

-- Hemantgiri S. Goswami
Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Data Platform Consultant
I am a Database Administrator by profession, and a student at a university called life by heart. I am passionate about SQL Server, photography, reading and sharing. Currently, I'm Data Platform Consultant @Pythian. I have been a Microsoft SQL Server MVP for four years, and a published author of the book - SQL Server 2008 High Availability. Keep in touch with me on twitter @hemantgirig

3 Comments. Leave new

FN_GET_SQL can disappoint too and it did for me a week ago ; it can do in some cases

– If the blocking process is sleeping or in background

– If the plan cost is low and was flushed of the cache.

In my case , it was also a 3rd party app using server-side API cursors, something implemented by drivers such as ODBC , JDBC

It’s a bad combination anyway with 12-years old SQL 2000


Hi Mohammed,

I will update blog post to reflect your suggestion, and update code also to filter only ‘runnable’


Hi Aaron,

I will highlight and update my blog post so that readers can see that this is for SQL 2000 :)

Thank you


Leave a Reply

Your email address will not be published. Required fields are marked *