/*********************************************************************** | |
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