Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
If you are going to invest time and effort in setting up alerts for other conditions, might I suggest a 3rd party tool instead of reinventing the wheel every time? :-) –  Aaron Bertrand Feb 7 '13 at 15:24
    
@Aaron Bertrand that sounds great. Are there any that you recommend? –  Lumpy Feb 7 '13 at 15:30
    
Well, yes. :-) sqlsentry.net/solutions-sql-server.asp Disclaimer: I work for SQL Sentry. Let me know if you have any questions about our software: abertrand AT sqlsentry DOT net. –  Aaron Bertrand Feb 7 '13 at 15:37
    
@Aaron Bertrand I can't stop smiliing. That was done very well. I'll look into it. Albeit I doubt I'll be able to get any expenditures past my boss at the moment. –  Lumpy Feb 7 '13 at 15:45
    
I try to not be spammy but our software really does solve this kind of problem well. –  Aaron Bertrand Feb 7 '13 at 15:59
add comment

2 Answers

up vote 4 down vote accepted

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](@obj nvarchar(max))
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/process/inputbuf)[1]', 'nvarchar(max)')
        SET @blocking_spid = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@spid)[1]', 'int')
        SET @blocking_hostname = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@hostname)[1]', 'nvarchar(128)')
        SET @blocking_db = DB_NAME(@message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@currentdb)[1]', 'int'))
        SET @blocking_login = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@loginname)[1]', 'nvarchar(128)')
        SET @blocking_lasttranstarted = CONVERT(varchar(32), @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@lasttranstarted)[1]', 'datetime'), 109)
        SET @blocking_inputbuf = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/inputbuf)[1]', 'nvarchar(max)')

        SELECT
            @waitresource_name = wait_resource_object_name,
            @waitresource_schema = wait_resource_schema_name,
            @waitresource_db = wait_resource_database_name
        FROM master.dbo.wait_resource_name(@waitresource)

        SET @mail_body = 'Posted: ' + ISNULL(@post_time, '') + CHAR(10) +
            'Duration: ' + ISNULL(CAST(@duration AS varchar) + ' s', '') + CHAR(10) +
            CHAR(10) +
            '==========Blocked Process==========' + CHAR(10) +
            'SPID: ' + ISNULL(CAST(@blocked_spid AS varchar), '') + CHAR(10) +
            'Wait Resource: ' + ISNULL(ISNULL(QUOTENAME(@waitresource_db) + '.' + QUOTENAME(@waitresource_schema) + '.' + QUOTENAME(@waitresource_name), @waitresource), '') + CHAR(10) +
            'Hostname: ' + ISNULL(@blocked_hostname, '') + CHAR(10) +
            'Current Database: ' + ISNULL(@blocked_db, '') + CHAR(10) +
            'Login Name: ' + ISNULL(@blocked_login, '') + CHAR(10) +
            'Last Transaction Started: ' + ISNULL(@blocked_lasttranstarted, '') + CHAR(10) +
            '----------Input Buffer----------' + CHAR(10) +
            ISNULL(@blocked_inputbuf, '') + CHAR(10) +
            CHAR(10) +
            '==========Blocking Process==========' + CHAR(10) +
            'SPID: ' + ISNULL(CAST(@blocking_spid AS varchar), '') + CHAR(10) +
            'Hostname: ' + ISNULL(@blocking_hostname, '') + CHAR(10) +
            'Current Database: ' + ISNULL(@blocking_db, '') + CHAR(10) +
            'Login Name: ' + ISNULL(@blocking_login, '') + CHAR(10) +
            'Last Transaction Started: ' + ISNULL(@blocking_lasttranstarted, '') + CHAR(10) +
            '----------Input Buffer----------' + CHAR(10) +
            ISNULL(@blocking_inputbuf, '') + CHAR(10)

        EXEC sp_send_dbmail @recipients = 'Your address here', @subject = 'Blocked Process Report', @body = @mail_body
    END
    ELSE IF @message_type IN ('http://schemas.microsoft.com/SQL/ServiceBroker/Error', 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
    BEGIN
        END CONVERSATION @dialog
    END

    COMMIT TRANSACTION
END

GO

ALTER QUEUE BlockedProcessQueue WITH ACTIVATION (
    STATUS = ON,
    PROCEDURE_NAME = [BlockedProcessActivationProcedure],
    MAX_QUEUE_READERS = 1,
    EXECUTE AS OWNER
)
share|improve this answer
add comment

Not sure if there is some easier way built in but one way would be to first configure the blocked process threshold to be 60 seconds.

sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
sp_configure 'blocked process threshold', 60 ;
GO
RECONFIGURE ;
GO

Then set up an Event Notification for the BLOCKED_PROCESS_REPORT. See this answer for example code for event notifications.

Your activation procedure could use sp_send_dbmail to send the email.

share|improve this answer
    
Great post I have used the blocked process report before. Is there any way to include the parameter values for the blocked/blocking statements shown by the report? Thanks –  user18933 Feb 7 '13 at 22:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.