IF EXISTS (SELECT * FROM sysobjects 
           WHERE id = object_id('dbo.check_for_blocks') 
           AND sysstat & 0xf = 4)
	DROP PROCEDURE dbo.check_for_blocks
GO

CREATE PROCEDURE check_for_blocks
    @taskName VARCHAR(30) = "Block Monitor",
    @userName VARCHAR(30) = "<your important user>",
    @mailTo VARCHAR(30) = "DBA"
AS
/* see if any important users are blocked */
DECLARE @CMD VARCHAR(255),
        @CMDstub VARCHAR(255), 
        @CMDstatus INT,
        @localCMDstatus INT,
        @spid SMALLINT,
        @dbid SMALLINT,
        @blocker SMALLINT,
        @blockersuid SMALLINT

SET NOCOUNT ON

/* only allow one instance of the block monitor to run */
IF (SELECT COUNT(*) 
    FROM master..sysprocesses 
    WHERE spid <> @@SPID
    AND program_name LIKE ("SQLEXEC - TSQL Task #%")
    AND CONVERT(INT,SUBSTRING(RTRIM(program_name), 
                CHARINDEX("#", program_name) + 1, 
                DATALENGTH(program_name) - CHARINDEX("#", program_name))) 
                IN (SELECT id FROM msdb..systasks
                    WHERE name = (@taskName))) = 0
  BEGIN
    /* get the process id and user id for the first block encountered */
    SELECT @spid = spid, @dbid = dbid, @blocker = blocked
    FROM master..sysprocesses
    WHERE blocked <> 0 
    AND suid = SUSER_ID(@userName) 
    /* if no blocks, we're done */
    IF @spid IS NOT NULL
      BEGIN      
        /* wait five seconds, if still blocked take action */
        WAITFOR DELAY "00:00:10"        
        SELECT @spid = spid, @dbid = dbid, @blocker = blocked
        FROM master..sysprocesses
        WHERE blocked <> 0 
        AND suid = SUSER_ID(@userName) 
        IF @spid IS NOT NULL
          BEGIN
            SELECT @blockersuid = suid FROM master..sysprocesses WHERE spid = @blocker
            SELECT @CMDstatus = 0  
            SELECT @CMDstub = "Login " + UPPER(@UserName) + " (spid " + CONVERT(VARCHAR(3),@spid) + 
                              ") blocked in database " + UPPER(DB_NAME(@dbid)) + " by login " + 
                              UPPER(SUSER_NAME(@blockersuid)) + " (spid " + CONVERT(VARCHAR(3),@blocker) 
                              + "). [admin.check_for_blocks] " + CONVERT(VARCHAR(20), getdate()) 
            SELECT @CMD = "net send bwunder " + @CMDstub
            EXEC @localCMDstatus = master..xp_cmdshell @CMD, no_output
            IF (@localCMDStatus <> 0) SELECT @CMDstatus = @CMDstatus + 1 
            /* add more folks if you want them to get the message */
/*
            SELECT @CMD = "net send xxxxxxx " + @CMDstub
            EXEC @localCMDstatus = master..xp_cmdshell @CMD, no_output
            IF (@localCMDStatus <> 0) SELECT @CMDstatus = @CMDstatus + 1 
*/
            IF (@CMDstatus <> 0) -- failed

              BEGIN
                SELECT @CMD = CONVERT(CHAR(1),@CMDStatus) + 
                              " block alert network broadcast messages FAILED!" + 
                              " (admin..check_for_blocks)"
                RAISERROR(@CMD,1,2) WITH LOG
              END

            /* send a report of the blocking hierarchy */
            EXEC master..xp_sendmail @recipients = @mailTo, 
                                     @subject = 'Blocking Report',  
                                     @message = @CMDstub,
                                     @query = 'admin..find_blocker'         
            /* wait one minute so the block messages don't flood 
               the network with messages */ 
            WAITFOR DELAY "00:01:00"        
          END -- send messages
      END  -- blocks found
  END  -- block monitor already running

EXEC msdb..sp_purgehistory @taskName -- system procedure, see BOL
GO