!Xevents-Trace-Deadlock-Report

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

/***********************************************************************
COLLECT DEADLOCK GRAPHS WITH EXTENDED EVENTS

This script contains TSQL to:
	* Create an Extended Events Trace collecting sqlserver.xml_deadlock_report
	* Start the trace
	* Code to stop and delete the trace is commented out

Notes:
	This works with SQL Server 2012 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 [Deadlock Report] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file
	(SET filename=
		N'S:\XEvents\deadlock-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=30 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 [Deadlock Report] 
	ON SERVER  
	STATE = START;  
GO


/***********************************************************************
Test a deadlock with the code here:
	https://www.littlekendra.com/2016/09/13/deadlock-code-for-the-wideworldimporters-sample-database/
***********************************************************************/



/* Stop the Extended Events trace when you want with a command like this */

--ALTER EVENT SESSION [Deadlock Report]  
--	ON SERVER  
--	STATE = STOP;  
--GO

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

--DROP EVENT SESSION [Deadlock Report] ON SERVER;
--GO

Server-Side-SQL-Trace-Deadlock

/***********************************************************************
THIS OPTION USES A SERVER SIDE SQL TRACE TO PICK UP THE BPR.
YOU ONLY NEED THIS *OR* ONE OF THE XEVENTS TRACES
***********************************************************************/


/* Modified from a script generated from SQL Server Profiler */
/* Pre-requisites and notes: 
	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)
  Code to stop and delete the trace is commented out at the end

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\Deadlock-Graph';
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 deadlock graph event and collect some columns */
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 148, 1, @on
exec sp_trace_setevent @TraceID, 148, 12, @on
exec sp_trace_setevent @TraceID, 148, 14, @on


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

/* Return list of traces to the caller */
select * from sys.traces;
GOTO finish

error: 
select ErrorCode=@rc

finish: 
GO



/***********************************************************************
Test a deadlock with the code here:
	https://www.littlekendra.com/2016/09/13/deadlock-code-for-the-wideworldimporters-sample-database/
***********************************************************************/



/***********************************************************************
STOP AND DELETE THE Deadlock-Graph Trace (when needed)
***********************************************************************/

--/* Get the trace id */
--DECLARE @id int
--SELECT @id=id
--FROM sys.traces where path like '%Deadlock-Graph%';

--/* Stop the trace by setting its status to 0 */
--EXEC sp_trace_setstatus @traceid = @id , @status = 0; 

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

--/* Return remaining traces */
--SELECT * FROM sys.traces;
--GO

Xevents-Trace-Errors-Statements-Deadlock-Report

/***********************************************************************
COLLECT ERRORS, STATEMENTS, AND DEADLOCK GRAPHS WITH EXTENDED EVENTS
VERY VERBOSE

This script contains TSQL to:
	* Create an Extended Events Trace collecting 
		errors, completed statements, and deadlock report
	* Trace collects transaction_id on statement completed events, this
		can be matched to xactid for each item in the Blocked Process Report
	* Start the trace
	* Code to stop and delete the trace is commented out at the end

Notes:
	This works with SQL Server 2012 and higher
	*** This will generate a LOT of data on most servers and may slow performance
		due to collecting statement completed events. If in doubt, start
		with the alternate script collecting sqlserver.xml_deadlock_report only. ***
	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 [Errors + Statements + Deadlock Report] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.sql_text,sqlserver.transaction_id)),
ADD EVENT sqlserver.sp_statement_completed(SET collect_statement=(1)
    ACTION(sqlserver.sql_text,sqlserver.transaction_id)),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.sql_text,sqlserver.transaction_id)),
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file
	(SET filename=
		N'S:\XEvents\errors-statements-deadlock-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=30 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 [Errors + Statements + Deadlock Report] 
	ON SERVER  
	STATE = START;  
GO


/***********************************************************************
Test a deadlock with the code here:
	https://www.littlekendra.com/2016/09/13/deadlock-code-for-the-wideworldimporters-sample-database/
***********************************************************************/



/* Stop the Extended Events trace when you want with a command like this */

--ALTER EVENT SESSION [Errors + Statements + Deadlock Report]  
--	ON SERVER  
--	STATE = STOP;  
--GO

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

--DROP EVENT SESSION [Errors + Statements + Deadlock Report] ON SERVER;
--GO