Using DBCC INPUTBUFFER for SQL Server troubleshooting

dba toolboxIn my last post on sp_who2 I demonstrated how this handy utility can help you in your troubleshooting efforts.

Continuing on from this, I am going to cover DBCC INPUTBUFFER

What is DBCC INPUTBUFFER ?

It is a command used to identify the last statement executed by a particular SPID. You would typically use this after running sp_who2

How to use

For example, I have just run run sp_who2 and identified that my lead blocker is SPID number 54 so I would run as follows:

T-SQL:

DBCC INPUTBUFFER(54)

Here is a simple demonstration. In a new query window run this

BEGIN
UPDATE Orders SET Amount = 500 WHERE OrderID = 1;

Now in a second query window run this

UPDATE Orders SET Amount = 200 WHERE OrderID = 1;

Now run sp_who2 to identify the lead blocker followed by DBCC INPUTBUFFER using the lead blocker SPID. Here are my results with the column “EventInfo” providing the detail that I need on the T-SQL which is causing the problem. It is possible to copy and paste the code into a query window for analysis.

using dbcc inputbuffer for sql server troublshooting

Permissions needed for DBCC INPUTBUFFER

You need to be a sysadmin or have the VIEW SERVER STATE permission to be able to run this command.

Conclusion

Again, like sp_who2, this handy command should be in your “DBA Toolbox”.

UJNZMQQN5JCV

It's only fair to share...Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInPin on PinterestBuffer this pageShare on RedditShare on StumbleUpon
About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on

Speak Your Mind

*


*