How to monitor Blocked Processes with SQL Alert and email sp_whoisActive report

03 January,2017 by Jack Vamvas

Question: How can I monitor SQL Server blocked processes with a SQL Server alert and capture the details for analysis. I’d like the alert to trigger when there is more than one blocked process.

When the blocked process triggers the SQL alert, I want sp_whoisActive to execute , capture details and email the report.

sp_whoisActive is one of the tools I use  when troubleshooting SQL Server performance issues

Read more on sp_WhoIsActive utility for SQL Server troubleshooting

Answer: There are multiple ways of achieving this goal. Monitoring SQL blocked processes is an excellent way of identifying tuning opportunities.

The method I’m proposing includes a) Create a SQL Server Job which will manage the execution of the sp_whoisActive stored procedure

b)Create a SQL alert . The SQL Alert has a performance condition, if the condition is satisfied, than trigger the execution of the SQL Server Agent Job

 

Step 1 : Create a SQL Server Agent Job. The SQL Server Agent Job will  trigger the sp_whoisActive stored procedure . You’ll need to modify some details:

@recipients

@profile_name

 

declare @subject varchar(max),
 @cmd varchar(max),
 @file varchar(100)
 select    @subject = @@servername  + ' Encountered Blocking sessions',
 @cmd = 'exec master.dbo.sp_WhoIsActive'

exec msdb.dbo.sp_send_dbmail
 @profile_name = 'Main',
 @recipients = 'jack@sqlserver-dba.com',
 @subject = @subject,
 @body= '',
 @query = @cmd,
 @query_result_separator = '|',
 @query_result_no_padding = 1

 

Step 2 : Create an SQL alert . A couple things which you can adjust. The @performance condition , accesses a perfmon counter for a specific SQL Server Instance : MSSQL$DBA_TEST. Adjust this reference for your own purposes.

The @job_id is the GUID for the SQL Server Agent Job created in Step 1

 

 

USE [msdb]
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Report Blocked Processes',
 @message_id=0,
 @severity=0,
 @enabled=1,
 @delay_between_responses=0,
 @include_event_description_in=0,
 @database_name=N'',
 @notification_message=N'',
 @event_description_keyword=N'',
 @performance_condition=N'MSSQL$DBA_TEST:General Statistics|Processes blocked||>|1',
@job_id=N'FEFBB19E-39AD-4818-9EFF-25457B2F162E'
GO


Read More

 Blocked Process Report and how to read (SQL Server DBA)

SQL Server – Understand Key wait resource in Blocking (SQL ...

SQL Server – Find queries causing Lock Timeouts per second

SQL Server - How to detect and troubleshoot blocking and deadlocks


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to monitor Blocked Processes with SQL Alert and email sp_whoisActive report


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer