Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

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!
0
T-Virus
Asked:
T-Virus
1 Solution
 
rob_farleyCommented:
You need to use the RECEIVE command to pop stuff off the queue (see Books Online for lots of examples)

Then you can use sp_send_dbmail to send the email.

Rob
0
 
T-VirusAuthor Commented:
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
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now