identify blocking and send alert

I need to create an alert that will notify me when any query has been blocked for more than 60 seconds. For example if someone has a transaction open on a table and forgets to run a commit or a rollback. Is this possible to get from the system tables?

I liked Martin's suggestion of using event notifications, so I followed his example link, and put this together for our server. You'll need to either put your email address in the call to sp_send_dbmail, or modify the procedure to read email addresses from a config table of some sort. Also, adjust the blocked process threshold to your liking. The result is a concise report message, with info about the blocked and blocking processes.

The error handling is really basic; it just ends the conversation if it receives an error message or end dialog message.

Make sure Database Mail is configured on your system to allow the use of sp_send_dbmail.

```
USE msdb
GO

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'blocked process threshold', 30
RECONFIGURE
GO

CREATE QUEUE BlockedProcessQueue

CREATE SERVICE BlockedProcessService ON QUEUE BlockedProcessQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO

USE master
GO

CREATE FUNCTION [dbo].wait_resource_name)
RETURNS @wait_resource TABLE (
wait_resource_database_name sysname,
wait_resource_schema_name sysname,
wait_resource_object_name sysname
)
AS
BEGIN
DECLARE @dbid int
DECLARE @objid int

IF @obj IS NULL RETURN
IF @obj NOT LIKE 'OBJECT: %' RETURN

SET @obj = SUBSTRING(@obj, 9, LEN(@obj) - 9 + CHARINDEX(':', @obj, 9))

SET @dbid = LEFT(@obj, CHARINDEX(':', @obj, 1) - 1)
SET @objid = SUBSTRING(@obj, CHARINDEX(':', @obj, 1) + 1, CHARINDEX(':', @obj, CHARINDEX(':', @obj, 1) + 1) - CHARINDEX(':', @obj, 1) - 1)

INSERT INTO @wait_resource (wait_resource_database_name, wait_resource_schema_name, wait_resource_object_name)
SELECT db_name(@dbid), object_schema_name(@objid, @dbid), object_name(@objid, @dbid)

RETURN
END
GO

CREATE PROCEDURE StartBlockedProcessNotification
AS
CREATE EVENT NOTIFICATION BlockedProcessNotification ON SERVER FOR BLOCKED_PROCESS_REPORT TO SERVICE 'BlockedProcessService', 'current database'
GO

EXEC sp_procoption 'StartBlockedProcessNotification', 'startup', 'on'
EXEC StartBlockedProcessNotification

USE msdb
GO

CREATE PROCEDURE BlockedProcessActivationProcedure
AS

--Service Broker
DECLARE @message_body xml
DECLARE @message_body_text nvarchar(max)
DECLARE @dialog uniqueidentifier
DECLARE @message_type nvarchar(256)

WHILE 1 = 1
BEGIN --Process the queue
BEGIN TRANSACTION;

RECEIVE TOP (1)
@message_body = message_body,
@dialog = conversation_handle,
@message_type = message_type_name
FROM BlockedProcessQueue

IF @@ROWCOUNT = 0
BEGIN
RAISERROR('Nothing more to process', 0, 1)
ROLLBACK TRANSACTION
RETURN
END

IF @message_type = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification'
BEGIN
DECLARE @mail_body nvarchar(max)

DECLARE @post_time varchar(32)
DECLARE @duration int
DECLARE @blocked_spid int
DECLARE @waitresource nvarchar(max)
DECLARE @waitresource_db nvarchar(128)
DECLARE @waitresource_schema nvarchar(128)
DECLARE @waitresource_name nvarchar(128)
DECLARE @blocked_hostname nvarchar(128)
DECLARE @blocked_db nvarchar(128)
DECLARE @blocked_login nvarchar(128)
DECLARE @blocked_lasttranstarted nvarchar(32)
DECLARE @blocked_inputbuf nvarchar(max)
DECLARE @blocking_spid int
DECLARE @blocking_hostname nvarchar(128)
DECLARE @blocking_db nvarchar(128)
DECLARE @blocking_login nvarchar(128)
DECLARE @blocking_lasttranstarted nvarchar(32)
DECLARE @blocking_inputbuf nvarchar(max)

SET @post_time = CONVERT(varchar(32), @message_body.value(N'(//EVENT_INSTANCE/PostTime)[1]', 'datetime'), 109)
SET @duration = CAST(@message_body.value(N'(//EVENT_INSTANCE/Duration)[1]', 'bigint') / 1000000 AS int)
SET @blocked_spid = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@spid)[1]', 'int')
SET @waitresource = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@waitresource)[1]', 'nvarchar(max)')
SET @blocked_hostname = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@hostname)[1]', 'nvarchar(128)')
SET @blocked_db = DB_NAME(@message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@currentdb)[1]', 'int'))
SET @blocked_login = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@loginname)[1]', 'nvarchar(128)')
SET @blocked_lasttranstarted = CONVERT(varchar(32), @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@lasttranstarted)[1]', 'datetime'), 109)
SET @blocked_inputbuf = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/pr

StackExchange Database Administrators Q#34313, answer score: 7