Wednesday, March 7, 2012

Blocked transactions - how to identify the sql commands?

Hi SQLServer gurus :)

Just wondering if anyone can give me some info on how to find out the full syntax of the commands executed by the blocked and blocking SPID's in a locking situation.

Using sp_who or sp_who2 will give basic info on the blocked trans (such as DELETE, SELECT etc), but not the actual statement. The blocking spid's command is only showing AWAITING COMMAND.

Not really an urgent problem, but any suggestions appreciated!

Cheers,
MeganDBCC INPUTBUFFER
Displays the last statement sent from a client to Microsoft SQL Server.

Syntax
DBCC INPUTBUFFER (spid)

You can use this command to see what is the longest running command which may point to the transaction that is causing the blocking.

DBCC OPENTRAN
Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database. Results are displayed only if there is an active transaction or if the database contains replication information. An informational message is displayed if there are no active transactions.

Syntax
DBCC OPENTRAN
( { 'database_name' | database_id} )
[ WITH TABLERESULTS
[ , NO_INFOMSGS ]
]|||Thanks for your response, achorozy.

No comments:

Post a Comment