asked on

Blocked Processes

Hi,

I am trying to send a automated e-mail as soon as there is a blocked process on the server&

As usual&
sp_configure 'show advanced options', 1
sp_configure 'blocked process threshold', 10 &

For this I created a service broker queue to hold the event
/* CREATE QUEUE eventblockedprocess */

Then created a broker service to receive the event
/* CREATE SERVICE eventblockedprocess
ON QUEUE eventblockedprocess ( [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] ) */

An created a event notification
/* CREATE EVENT NOTIFICATION notify_locks
ON SERVER
WITH fan_in
FOR blocked_process_report
TO SERVICE ' eventblockedprocess', 'current database';*/

By executing &
/* SELECT cast( message_body as xml )AS [XML]
FROM msdb.dbo.eventblockedprocess */
I can see all the blocked processes

Now I would like to send a E-Mail as soon as there is a entry in the queue
And attache it as xml file to the mail.

Do you know how to do?
Thanks!

ASKER CERTIFIED SOLUTION

Rob Farley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

T-Virus

ASKER

For all who are searching for a solution i did it this way...

First activate feature

sp_configure 'show advanced options', 1
go
sp_configure 'blocked process threshold', 10
go
reconfigure
go
sp_configure
go

2nd Step create queue:

USE MSDB
CREATE QUEUE eventblockedprocess
go
CREATE SERVICE eventblockedprocess
ON QUEUE eventblockedprocess ( [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] )
go
CREATE EVENT NOTIFICATION notify_locks
ON SERVER
WITH fan_in
FOR blocked_process_report
TO SERVICE 'eventblockedprocess', 'current database';
go

Then create a job and start it:

start:
DECLARE @msgs TABLE (Message_Body XML);
WAITFOR (RECEIVE TOP(10) message_body FROM dbo.eventblockedprocess INTO @msgs);
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Blocked Process Report</H1>' +
N'<table border="1">' +
N'<tr><th>DB ID</th><th>Blocked Query ID</th>' +
N'<th>Duartion</th><th>Blocked by Query</th>' +
CAST ( ( SELECT
                  td = cast( message_body as xml ).value( '(/EVENT_INSTANCE/DatabaseID)[1]', 'int' ), '',
                  td = cast( message_body as xml ).query( '/EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process' ), '',
                  td = cast( message_body as xml ).value( '(/EVENT_INSTANCE/Duration)[1]', 'Numeric'), '',
                  td = cast( message_body as xml ).query( '/EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process' ), ''
                  FROM @msgs FOR XML PATH ('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'xxxx',
      @recipients = 'xxxx@xxxxx',
      @subject = 'Blocked Process',
      @body = @tableHTML,
      @body_format = 'HTML'

DELETE FROM @msgs;
      GOTO start