Post reply

Reporting on blocking transactions

  • November 20, 2009 at 3:09 pm

    #143097

    Is there a way to receieve an email when a transaction stays blocked for more than say 15seconds? Also, is there a way to generate a report to look at this information historically?

  • December 4, 2009 at 12:58 pm

    #1088001

    [font="Arial"]Regarding blocking SPIDs, here is a query that I use:[/font]

    SELECT TOP 1

    &#160 session_id AS Blocked_SPID,

     &#160blocking_session_id AS Blocking_SPID,

     &#160wait_time

    FROM sys.dm_exec_requests

    WHERE wait_time >= 15000 --15 seconds or more

    AND blocking_session_id != 0

    ORDER BY

     &#160wait_time DESC,

     &#160--Sort by session_id % blocking_session_id DESC, otherwise it may

     &#160--appear that the blocked and blocking spids are the same.

     &#160session_id % blocking_session_id DESC

    [font="Arial"]I created a stored procedure that uses the query above. It sends an email via stored proc msdb.dbo.sp_send_dbmail whenever the query returns any rows. I also created a job that executes the sp every 5 mins during "office hours".

    sys.dm_exec_requests does not contain historical info...[/font]

    Dave MasonSeminole County, FL

  • December 6, 2009 at 9:39 am

    #1088271

    I built a utility based on some code I found a few years ago. It captures blocking info, including blocker, blockee, what they are running, time, spid etc... sends an email, and writes it to a table so I can look at it later. I've found it extremely useful, especially for troubleshooting blocking after the fact, since I may not be aware of it while it's happening (in a meeting, on the phone, off hours ....).

    The code is hard to follow, and I've modified it a bit to capture a bit more info. You'll need to modify the email send portion to replace my custom email SP. I run it in a job every minute. I can't take much credit or blame for the bulk of the code. It does what I need, so it is what it is.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply