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
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:
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 = 'email@example.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