Skip to content

Instantly share code, notes, and snippets.

@LitKnd
Last active September 14, 2023 22:49
Show Gist options
  • Save LitKnd/19a5942e2527af3e90692c5145c3a059 to your computer and use it in GitHub Desktop.
Save LitKnd/19a5942e2527af3e90692c5145c3a059 to your computer and use it in GitHub Desktop.
/***********************************************************************
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
/***********************************************************************
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
/***********************************************************************
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment