Blocked Process Report - Configuring and Tracing - SQL Server

!configure-blocked-process-report.sql

/***********************************************************************
Copyright 2016, Kendra Little - littlekendra.com
MIT License, http://www.opensource.org/licenses/mit-license.php
***********************************************************************/

/***********************************************************************
FIRST, TELL SQL SERVER TO ISSUE THE BLOCKED PROCESS REPORT
***********************************************************************/

/* Check if there are any pending configuration items before you start */
/* Take care of those before proceeding if they exist */

SELECT *
FROM sys.configurations
where value <> value_in_use;
GO

/* Show 'advanced options' -- the BPR setting is advanced!  */
/* Warning: RECONFIGURE pushes through ALL pending changes! */

IF (SELECT value_in_use FROM sys.configurations
	where name=N'show advanced options') <> 1
BEGIN
	EXEC ('EXEC sp_configure ''show advanced options'', 1;');
	EXEC ('RECONFIGURE');
END


/* Set the blocked process threshold (seconds) to a value of 5 */
/* or higher to tell SQL Server to issue blocked process reports. */
/* Set this back to 0 at any time to stop blocked process reports. */
EXEC sp_configure 'blocked process threshold (s)', 5;
GO
RECONFIGURE;
GO


/* You're not done-- you must configure a trace to pick up the 
Blocked Process Report. 
You may use either:
	* SQL Trace (server side trace recommended)
	* Extended Events 
*/

2.a.create-extended-events-blocked-process-report.sql

/***********************************************************************
NEXT, TRACE THE BLOCKED PROCESS REPORT
THIS OPTION USES XEVENTS.
YOU CAN USE A SERVER SIDE SQL TRACE INSTEAD THOUGH (SCROLL DOWN)
***********************************************************************/


/* Pre-requisites and notes: 
	Configure 'blocked process threshold (s)' to 5 or higher in sp_configure
	This works with SQL Server 2014 and higher
	Change the filename to a relevant location on the server itself 
	Tweak options in the WITH clause to your preference
	Note that there is no automatic stop for this! If you want that, use a 
		Server Side SQL Trace instead.

	THIS CREATES AND STARTS AN EXTENDED EVENTS TRACE
*/


/* Create the Extended Events trace */
CREATE EVENT SESSION [Blocked Process Report] ON SERVER 
ADD EVENT sqlserver.blocked_process_report
ADD TARGET package0.event_file
	(SET filename=
		N'S:\XEvents\Blocked-Process-Report.xel', max_file_size=(1024),max_rollover_files=(4))
		/* File size is in MB */
WITH (
	MAX_MEMORY=4096 KB,
	EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
	MAX_DISPATCH_LATENCY=120 SECONDS /* 0 = unlimited */,
	MAX_EVENT_SIZE=0 KB,
	MEMORY_PARTITION_MODE=NONE,
	TRACK_CAUSALITY=OFF,
	STARTUP_STATE=ON)
GO


/* Start the Extended Events trace */
ALTER EVENT SESSION [Blocked Process Report]  
	ON SERVER  
	STATE = START;  
GO

/* Drop the trace when you're done with a command like this:

DROP EVENT SESSION [Blocked Process Report] ON SERVER 
GO
 */

2.b.create-server-side-sql-trace-blocked-process-report.sql

/***********************************************************************
THIS OPTION USES A SERVER SIDE SQL TRACE TO PICK UP THE BPR.
YOU ONLY NEED THIS *OR* THE XEVENTS TRACE -- NOT BOTH
***********************************************************************/

/* Modified from a script generated from SQL Server Profiler */
/* Pre-requisites and notes: 
	Configure 'blocked process threshold (s)' to 5 or higher in sp_configure
	This works with SQL Server 2005 and higher
	Change the filename to a relevant location on the server itself 
	Tweak options to your preference (including the end date)

	THIS CREATES AND STARTS A SERVER SIDE SQL TRACE
*/

declare @rc int;
declare @TraceID int;
declare @maxfilesizeMB bigint;
declare @TraceEndDateTime datetime;
declare @TraceFilename nvarchar(500);
declare @rolloverfilecount int;

set @TraceEndDateTime = '2020-12-12 00:00:00.000';
set @maxfilesizeMB = 1024;
set @TraceFilename = N'S:\XEvents\Blocked-Process-Report';
set @rolloverfilecount = 4;

/* Create the basic server side trace */
exec @rc = sp_trace_create 
	@TraceID output, 
	@options = 2 /* trace will use rollover files */, 
	@tracefile = @TraceFilename, 
	@maxfilesize = @maxfilesizeMB, 
	@stoptime = @TraceEndDateTime,
	@filecount = @rolloverfilecount;

if (@rc != 0) goto error;

/* Add the blocked process report event and collect some columns */
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 3, @on
exec sp_trace_setevent @TraceID, 137, 12, @on
exec sp_trace_setevent @TraceID, 137, 15, @on
exec sp_trace_setevent @TraceID, 137, 26, @on

/* Start the trace */
exec sp_trace_setstatus @TraceID, 1

/* Return the trace id to the caller */
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

3.a.list-and-stop-extended-events-traces.sql

/***********************************************************************
NOT SURE HOW MANY XEVENTS TRACES YOU'RE RUNNING? 
USE THIS TO CHECK, AND POSSIBLY STOP AND DELETE.
***********************************************************************/

/* List Extended Events Traces which are currently started. 
Built-in sessions include:
	system_health
	sp_server_diagnostics session
	hkenginexesession
	telemetry_xevents
*/
SELECT
	name, 
	pending_buffers,
	create_time, 
	session_source
FROM sys.dm_xe_sessions;
GO

/* Plug the trace name you want to stop and drop into
the commands below */

ALTER EVENT SESSION [Blocked Process Report]  
	ON SERVER  
	STATE = STOP;  
GO

DROP EVENT SESSION [Blocked Process Report] ON SERVER 
GO

3.b.list-and-stop-server-side-sql-traces.sql

/***********************************************************************
NOT SURE HOW MANY SERVER SIDE TRACES OR PROFILER TRACES YOU'RE RUNNING? 
USE THIS TO CHECK, AND POSSIBLY STOP AND DELETE.
***********************************************************************/


/* Want to clean up a server side trace for the Blocked Process Report, or anything else?  */

/* This will list all Server Side Traces (whether or not they have started)    */
/* The default trace is usually trace id=1, 
	it will show as having no stop time and have a path like
	D:\MSSQL\DATA\MSSQL13.MSSQLSERVER\MSSQL\Log\log_123.trc 
*/
SELECT *
FROM sys.traces;
GO

/* To stop a trace, get the id from the query above */
/* Stop the trace by setting it to status = 0 */
EXEC sp_trace_setstatus @traceid = ? , @status = 0; 
GO

/* Delete the trace by setting the status to 2 */
EXEC sp_trace_setstatus @traceid = ? , @status = 2; 
GO