November 20, 2009 at 3:09 pm
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
[font="Arial"]Regarding blocking SPIDs, here is a query that I use:[/font]
SELECT TOP 1
  session_id AS Blocked_SPID,
 blocking_session_id AS Blocking_SPID,
 wait_time
FROM sys.dm_exec_requests
WHERE wait_time >= 15000--15 seconds or more
AND blocking_session_id != 0
ORDER BY
 wait_time DESC,
 --Sort by session_id % blocking_session_id DESC, otherwise it may
 --appear that the blocked and blocking spids are the same.
 session_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]
December 6, 2009 at 9:39 am
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