SQL Server Performance Forum – Threads Archive

Blocking / Deadlocks

Here’s a fun one for ya I’m trying to create a trigger on the master..sysprocesses table that will dynamically write an entry to a log table of some kind when a block/deadlock occurs. Here’s the catch though… I want it to tell me the last statement that the blocking SPID executed and the statement the blocked SPID executed. I’ve tried DBCC INPUTBUFFER(spid) but this is VERY hit or miss and it hasn’t worked yet for any of the blocks/deadlocks I’ve encountered so far. Any ideas? [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Thanks in advance DBAWinnipeg

sp_who2 give you the spid, but I don’t know how to implement in a trigger.
Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

A trigger can’t reference a system table. a
Refer to this KBA [ttp://support.microsoft.com/default.aspx?scid=kb;EN-US;251004] for ver.7 and this KA http://support.microsoft.com/default.aspx?scid=kb;en-us;271509] for SQL 2K. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

True, I have tried and failed in the past as well. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

…and not a good practice. <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> <hr noshade size="1">Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center>

]]>