Technical: Microsoft – SQL Server – Deadlocks – Capturing – Using “Server Side SQL Tracing”

Technical: Microsoft – SQL Server – Deadlocks – Capturing – Using “Server Side SQL Tracing”

Background

This is a follow-up to another post where we covered using DBCC Trace Flags to track deadlock.

That post URL is https://danieladeniji.wordpress.com/2014/02/22/technical-microsoft-sql-server-blocking-deadlocks/.

Purpose

In this post we will touch on how can use “Server side SQL Tracing” to capture identical diagnostic data.

Prepare Server Side Trace

  • Use menu items (File \ New Trace)
  • The “Connect to Server” window appear, choose your Server Type as “Database Engine”, enter your Server name, and “Authenticating Type”

Trace Properties – General

Item Name Item Data
Trace name SQL Deadlock
Trace provider name SQL Server Instance Name
 Use the template TSQL_Locks
 Save to File c:\temp\SQLDeadlock.trc
Trace provider name SQL Server Instance Name
 Use the template TSQL_Locks
 Save to File (Checkbox) Check the checkbox
Save to File (File name) Enter file name C:\temp\SQLDeadlock.trc
Enable fileover Check the “Enable fileover” checkbox

TraceProperties_General

Trace Properties – Events Selection

As we chose to use “TSQL_Locks” as our template, correlated events are pre-chosen for us.

As Deadlocks contain “plenty” of causative T-SQL statement, we do not need to track them.

If we chose to track them, we will add filters such as duration.

Again, we will filter them out by un-checking them.

  • Un-check “Stored Procedures – SP:StmtCompleted”
  • Un-check “Stored Procedures – SP:StmtStarting”
  • Un-check “TSQL – SP:StmtCompleted”
  • Un-check “TSQL – SP:StmtStarting”

TraceProperties_EventsSelection

Capture Trace Metadata

Let us capture the Trace Metadata

  • Use menu items “File\Export\Script Trace Definition\For SQL Server 2005 – 2008R2”

The resultant file looks like:


/****************************************************/
/* Created by: SQL Server 2008 R2 Profiler          */
/* Date: 02/25/2014  09:02:56 AM         */
/****************************************************/

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL 
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 137, 15, @on
exec sp_trace_setevent @TraceID, 137, 32, @on
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 13, @on
exec sp_trace_setevent @TraceID, 137, 22, @on
exec sp_trace_setevent @TraceID, 148, 11, @on
exec sp_trace_setevent @TraceID, 148, 12, @on
exec sp_trace_setevent @TraceID, 148, 14, @on
exec sp_trace_setevent @TraceID, 148, 1, @on
exec sp_trace_setevent @TraceID, 26, 15, @on
exec sp_trace_setevent @TraceID, 26, 32, @on
exec sp_trace_setevent @TraceID, 26, 1, @on
exec sp_trace_setevent @TraceID, 26, 9, @on
exec sp_trace_setevent @TraceID, 26, 57, @on
exec sp_trace_setevent @TraceID, 26, 2, @on
exec sp_trace_setevent @TraceID, 26, 10, @on
exec sp_trace_setevent @TraceID, 26, 11, @on
exec sp_trace_setevent @TraceID, 26, 35, @on
exec sp_trace_setevent @TraceID, 26, 12, @on
exec sp_trace_setevent @TraceID, 26, 13, @on
exec sp_trace_setevent @TraceID, 26, 6, @on
exec sp_trace_setevent @TraceID, 26, 14, @on
exec sp_trace_setevent @TraceID, 26, 22, @on
exec sp_trace_setevent @TraceID, 25, 15, @on
exec sp_trace_setevent @TraceID, 25, 32, @on
exec sp_trace_setevent @TraceID, 25, 1, @on
exec sp_trace_setevent @TraceID, 25, 9, @on
exec sp_trace_setevent @TraceID, 25, 57, @on
exec sp_trace_setevent @TraceID, 25, 2, @on
exec sp_trace_setevent @TraceID, 25, 10, @on
exec sp_trace_setevent @TraceID, 25, 11, @on
exec sp_trace_setevent @TraceID, 25, 35, @on
exec sp_trace_setevent @TraceID, 25, 12, @on
exec sp_trace_setevent @TraceID, 25, 13, @on
exec sp_trace_setevent @TraceID, 25, 6, @on
exec sp_trace_setevent @TraceID, 25, 14, @on
exec sp_trace_setevent @TraceID, 25, 22, @on
exec sp_trace_setevent @TraceID, 59, 32, @on
exec sp_trace_setevent @TraceID, 59, 1, @on
exec sp_trace_setevent @TraceID, 59, 57, @on
exec sp_trace_setevent @TraceID, 59, 2, @on
exec sp_trace_setevent @TraceID, 59, 14, @on
exec sp_trace_setevent @TraceID, 59, 22, @on
exec sp_trace_setevent @TraceID, 59, 35, @on
exec sp_trace_setevent @TraceID, 59, 12, @on
exec sp_trace_setevent @TraceID, 60, 32, @on
exec sp_trace_setevent @TraceID, 60, 9, @on
exec sp_trace_setevent @TraceID, 60, 57, @on
exec sp_trace_setevent @TraceID, 60, 10, @on
exec sp_trace_setevent @TraceID, 60, 11, @on
exec sp_trace_setevent @TraceID, 60, 35, @on
exec sp_trace_setevent @TraceID, 60, 12, @on
exec sp_trace_setevent @TraceID, 60, 6, @on
exec sp_trace_setevent @TraceID, 60, 14, @on
exec sp_trace_setevent @TraceID, 60, 22, @on
exec sp_trace_setevent @TraceID, 189, 15, @on
exec sp_trace_setevent @TraceID, 189, 32, @on
exec sp_trace_setevent @TraceID, 189, 1, @on
exec sp_trace_setevent @TraceID, 189, 9, @on
exec sp_trace_setevent @TraceID, 189, 57, @on
exec sp_trace_setevent @TraceID, 189, 2, @on
exec sp_trace_setevent @TraceID, 189, 10, @on
exec sp_trace_setevent @TraceID, 189, 11, @on
exec sp_trace_setevent @TraceID, 189, 35, @on
exec sp_trace_setevent @TraceID, 189, 12, @on
exec sp_trace_setevent @TraceID, 189, 13, @on
exec sp_trace_setevent @TraceID, 189, 6, @on
exec sp_trace_setevent @TraceID, 189, 14, @on
exec sp_trace_setevent @TraceID, 189, 22, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 9212c636-ceac-4558-8c82-32d0b6d3a223'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

Translate Trace Metadata into Stored Procedure

In my experience, it helps to encapsulate SQL statements into Stored Procedures. The reasons are plentiful, and they include:



use master
go

if object_id('dbo.sp_TraceCapture_Deadlock') is null
begin

	exec('create procedure dbo.sp_TraceCapture_Deadlock as select 1/0 as [shell] ')

end
go

alter procedure dbo.sp_TraceCapture_Deadlock 
(

	  @filename sysname
	, @maxfileSizeInMB bigint = 5
)
as

begin

	set nocount on;

	/****************************************************/
	/* Created by: SQL Server 2008 R2 Profiler          */
	/* Date: 02/25/2014  09:02:56 AM         */
	/****************************************************/

	-- Create a Queue
	declare @rc int
	declare @TraceID int
	declare @TraceStatus int

	declare @filenamelength int
	declare @filenameFolder sysname

	declare @SysTrace Table
	(
		  traceID int
		, [path]  sysname null
		, [folder] as SUBSTRING([path],1,LEN([path])-CHARINDEX('\',REVERSE([path]))) 
		, [status] int
	)

	declare @logMessage sysname

	declare @traceCreated bit

	set @traceCreated = 0
	set @TraceStatus = -1

	insert into @SysTrace
	(
		  traceID
		--, folder
		, [path]
		, [status]
	)
	select 
		   tblTrace.id
		 --, SUBSTRING(tblTrace.path,1,LEN(tblTrace.path)-CHARINDEX('\',REVERSE(tblTrace.path))) as folder
		 , tblTrace.path
		 , tblTrace.status
	from   sys.traces tblTrace

	if (@filename like '%.trc')
	begin

		print 'Filename needs to be stripped ' + @filename

		set @filenamelength = len(@filename)

		set @filename = left(@filename, @filenamelength - 4)

		print 'Filename stripped ' + @filename

	end

	/*
		Get folder for passed in folder
	*/
	set @filenameFolder = SUBSTRING(@filename,1,LEN(@filename)-CHARINDEX('\',REVERSE(@filename)))

	if exists
			(

				select *
				from   @SysTrace
				where  folder = @filenameFolder
			)
	begin

		set @traceCreated = 1

		set @logMessage = @filenameFolder + ' Trace already created '

		--raiserror(@logMessage, 16,1)
		--return -1

	end

	if (@traceCreated = 0)
	begin

		exec @rc = sp_trace_create 
                                 @TraceID output
                                , 0
                                , @filename
                                , @maxfilesizeInMB
                                , NULL 
		if (@rc != 0) goto error

		-- Client side File and Table cannot be scripted

		-- Set the events
		declare @on bit
		set @on = 1
		exec sp_trace_setevent @TraceID, 137, 15, @on
		exec sp_trace_setevent @TraceID, 137, 32, @on
		exec sp_trace_setevent @TraceID, 137, 1, @on
		exec sp_trace_setevent @TraceID, 137, 13, @on
		exec sp_trace_setevent @TraceID, 137, 22, @on
		exec sp_trace_setevent @TraceID, 148, 11, @on
		exec sp_trace_setevent @TraceID, 148, 12, @on
		exec sp_trace_setevent @TraceID, 148, 14, @on
		exec sp_trace_setevent @TraceID, 148, 1, @on
		exec sp_trace_setevent @TraceID, 26, 15, @on
		exec sp_trace_setevent @TraceID, 26, 32, @on
		exec sp_trace_setevent @TraceID, 26, 1, @on
		exec sp_trace_setevent @TraceID, 26, 9, @on
		exec sp_trace_setevent @TraceID, 26, 57, @on
		exec sp_trace_setevent @TraceID, 26, 2, @on
		exec sp_trace_setevent @TraceID, 26, 10, @on
		exec sp_trace_setevent @TraceID, 26, 11, @on
		exec sp_trace_setevent @TraceID, 26, 35, @on
		exec sp_trace_setevent @TraceID, 26, 12, @on
		exec sp_trace_setevent @TraceID, 26, 13, @on
		exec sp_trace_setevent @TraceID, 26, 6, @on
		exec sp_trace_setevent @TraceID, 26, 14, @on
		exec sp_trace_setevent @TraceID, 26, 22, @on
		exec sp_trace_setevent @TraceID, 25, 15, @on
		exec sp_trace_setevent @TraceID, 25, 32, @on
		exec sp_trace_setevent @TraceID, 25, 1, @on
		exec sp_trace_setevent @TraceID, 25, 9, @on
		exec sp_trace_setevent @TraceID, 25, 57, @on
		exec sp_trace_setevent @TraceID, 25, 2, @on
		exec sp_trace_setevent @TraceID, 25, 10, @on
		exec sp_trace_setevent @TraceID, 25, 11, @on
		exec sp_trace_setevent @TraceID, 25, 35, @on
		exec sp_trace_setevent @TraceID, 25, 12, @on
		exec sp_trace_setevent @TraceID, 25, 13, @on
		exec sp_trace_setevent @TraceID, 25, 6, @on
		exec sp_trace_setevent @TraceID, 25, 14, @on
		exec sp_trace_setevent @TraceID, 25, 22, @on
		exec sp_trace_setevent @TraceID, 59, 32, @on
		exec sp_trace_setevent @TraceID, 59, 1, @on
		exec sp_trace_setevent @TraceID, 59, 57, @on
		exec sp_trace_setevent @TraceID, 59, 2, @on
		exec sp_trace_setevent @TraceID, 59, 14, @on
		exec sp_trace_setevent @TraceID, 59, 22, @on
		exec sp_trace_setevent @TraceID, 59, 35, @on
		exec sp_trace_setevent @TraceID, 59, 12, @on
		exec sp_trace_setevent @TraceID, 60, 32, @on
		exec sp_trace_setevent @TraceID, 60, 9, @on
		exec sp_trace_setevent @TraceID, 60, 57, @on
		exec sp_trace_setevent @TraceID, 60, 10, @on
		exec sp_trace_setevent @TraceID, 60, 11, @on
		exec sp_trace_setevent @TraceID, 60, 35, @on
		exec sp_trace_setevent @TraceID, 60, 12, @on
		exec sp_trace_setevent @TraceID, 60, 6, @on
		exec sp_trace_setevent @TraceID, 60, 14, @on
		exec sp_trace_setevent @TraceID, 60, 22, @on
		exec sp_trace_setevent @TraceID, 189, 15, @on
		exec sp_trace_setevent @TraceID, 189, 32, @on
		exec sp_trace_setevent @TraceID, 189, 1, @on
		exec sp_trace_setevent @TraceID, 189, 9, @on
		exec sp_trace_setevent @TraceID, 189, 57, @on
		exec sp_trace_setevent @TraceID, 189, 2, @on
		exec sp_trace_setevent @TraceID, 189, 10, @on
		exec sp_trace_setevent @TraceID, 189, 11, @on
		exec sp_trace_setevent @TraceID, 189, 35, @on
		exec sp_trace_setevent @TraceID, 189, 12, @on
		exec sp_trace_setevent @TraceID, 189, 13, @on
		exec sp_trace_setevent @TraceID, 189, 6, @on
		exec sp_trace_setevent @TraceID, 189, 14, @on
		exec sp_trace_setevent @TraceID, 189, 22, @on

		-- Set the Filters
		declare @intfilter int
		declare @bigintfilter bigint

		exec sp_trace_setfilter 
		     @TraceID
		   , 10
		   , 0
		   , 7
		   , N'SQL Server Profiler - 9212c636-ceac-4558-8c82-32d0b6d3a223'

	end
	else
	begin

			select 
					  @TraceID = tblTrace.traceID
					, @TraceStatus = tblTrace.[status]
			from   @SysTrace tblTrace
			where  folder = @filenameFolder

	end

	-- Set the trace status to start
	if (@TraceStatus != 1)
	begin

		exec sp_trace_setstatus @TraceID, 1

		PRINT 'Trace Stated ' + convert(sysname, @TraceID)

	end

	-- display trace id for future references
	select TraceID=@TraceID

	goto finish

	error: 
	select ErrorCode=@rc

	finish: 

end

go

Test out Trace

Once you have your script packaged up as a Stored Procedure, run the stored procedure.  Doing so will start the trace as a background process.

To start the trace:

declare @filename sysname
declare @maxfileSizeInMB bigint

set @filename = 'C:\temp\SQLServerTracing'
set @maxfileSizeInMB = 5
exec dbo.sp_TraceCapture_Deadlock 
	  @filename = @filename
	, @maxfileSizeInMB = @maxfileSizeInMB

Review running Traces

To view running traces, please issue:



select 
	  tblTrace.id

	 , SUBSTRING(tblTrace.path,1
               ,LEN(tblTrace.path)-CHARINDEX('\',REVERSE(tblTrace.path))) as folder

	 , tblTrace.path

	 , tblTrace.status

from   sys.traces tblTrace

Image:

ReviewTraces

Stop running Traces

To view running traces, please issue:


select 
	  tblTrace.id

	 , SUBSTRING(tblTrace.path,1
               ,LEN(tblTrace.path)-CHARINDEX('\',REVERSE(tblTrace.path))) as folder

	 , tblTrace.path

	 , tblTrace.status

from   sys.traces tblTrace

Image:

listSQLServerTracesOnComfort

once you have the list of running traces, you stop the ones we no longer need:

Syntax:


exec sp_trace_setstatus [ @traceid = ] trace_id , [ @status = ] status

Sample:


declare @traceid int
exec sp_trace_setstatus @traceid = @trace_id , @status = 0

Deployment Choices

You can deploy through a variety of means:

  • SQL Server Agent  – Auto run job
  • SQL Server Engine – Auto Start Stored Procedure

Deployment Choices – SQL Server Agent

Create SQL Server Agent Job:

Tab – General

  • Name :- SQL Server – Deadlock Capture
  • Owner :- sa
  • Category : Database Maintenance

SQLServerAgent-Tab-General

Tab – Steps

We created two steps:

Step 1

  • Step Number :- 1
  • Name :- Say Hello
  • Type :- Transact-SQL Script
  • Database :- Master
  • Command :- print hello


Step 2

  • Step Number :- 2

  • Name :- Initiate dbo.sp_TraceCapture_Deadlock
  • Type :- Transact-SQL Script
  • Database :- Master
  • Command :-
    declare @filename sysname
    declare @maxfileSizeInMB intset @filename = ‘c:\temp\sammie.trc’
    set @maxfileSizeInMB = 5exec master.dbo.sp_TraceCapture_Deadlock
                 @filename = @filename
               , @maxfileSizeInMB = @maxfileSizeInMB

Job List

SQLServerAgent-Tab-Steps-List

Job Step – 1

SQLServerAgent-Tab-Steps-Step1

Job Step – 2

SQLServerAgent-Tab-Steps-Step2

Tab – Schedule

  • Name :- SQL Server Agent Starts
  • Schedule Type :- Start Automatically when SQL Server Agent starts
  • Enabled :- Checked

SQLServerAgent-Tab-Schedule

Tab – Notifications

Here is the “Job Notification” Tab.

  • We have checked the “Write to the Windows Application event log” check-box.  This will ensure that that monitoring jobs will pick up failures.

SQLServerAgent-Tab-Notifications

Script created job

Just as we scripted the SQL Server Trace definition, let us do same for the SQL Server Agent Job.

Doing so helps us:

  • Quickly and accurately reproduce on other servers
  • Keep in Version Control System

Job –



USE [msdb]
GO

/****** Object:  Job [SQL Server - Deadlock Capture]    Script Date: 2/25/2014 1:39:43 PM ******/

/*
EXEC msdb.dbo.sp_delete_job @job_id=N'dc727424-8843-4af4-8405-9f094f9857f0', @delete_unused_schedule=1
*/

GO

/****** Object:  Job [SQL Server - Deadlock Capture]    Script Date: 2/25/2014 1:39:43 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]    Script Date: 2/25/2014 1:39:43 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

if exists
	(
		select *
		from   msdb.dbo.sysjobs
		where  name = N'SQL Server - Deadlock Capture'
	)
begin
	EXEC msdb.dbo.sp_delete_job 
		  @job_name = N'SQL Server - Deadlock Capture'
		, @delete_unused_schedule=1
end

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'SQL Server - Deadlock Capture', 
		@enabled=1, 
		@notify_level_eventlog=2, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'Database Maintenance', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Say hello]    Script Date: 2/25/2014 1:39:43 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Say hello', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'print ''hello''', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Initiate dbo.sp_TraceCapture_Deadlock]    Script Date: 2/25/2014 1:39:43 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Initiate dbo.sp_TraceCapture_Deadlock', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'declare
         @filename sysname
        , @maxfileSizeInMB int = 5

set @filename = ''c:\temp\sammie.trc''
set @maxfileSizeInMB = 5

exec master.dbo.sp_TraceCapture_Deadlock 
         @filename = @filename
	, @maxfileSizeInMB = @maxfileSizeInMB
', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'SQL Server Agent Starts', 
		@enabled=1, 
		@freq_type=64, 
		@freq_interval=0, 
		@freq_subday_type=0, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20140225, 
		@active_end_date=99991231, 
		@active_start_time=0, 
		@active_end_time=235959, 
		@schedule_uid=N'65e1f81d-8d8c-4a81-87d5-505f9131a56c'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Review generated trace files

With time hopefully you will have some deadlock contents generated and captured.

Using SQL Server Profiler, let us review our deadlock data

  • Let us initiate SQL Server Profiler
  • Once “SQL Server Profiler” is running use menu items “File\Open Trace File…”
  • Guided by the file name you chose for your trace file, select the Trace file

SQL Server Captured Trace

SQLServerProfiler-CapturedTrace

The entries that contain the most useful data are those ones registered with “Deadlock graph“.

You want to click on the circles.  And, upon doing so context-sensitive balloons that showcases the “implicated” SQL statement appear.

Clean-up

Once you are done with your deadlock study, please free free to stop running traces and disable scheduled jobs.

Quick Compare of Deadlock Detection Mechanisms

The earlier post touches on how to log deadlocks in SQL Server Error Log using DBCC Trace Flags.

This post talks about how to do so using Server Side Trace Files.

I slightly prefer this approach as data captured are kept in their own log file and location.

And, thus the data captured can be consumed and analyzed as so you please.

Source Code Listing

Item Name Item Data
Captured SQL Server Deadlock Trace (do not use) SQLServerDeadlockServerTrace.sql
Stored Procedure  dbo.sp_TraceCapture_Deadlock.sql
Database Job DBJob – SQLServerDeadlockCapture.sql

Source Code Repository

The code placed inline in this post are initial versions.  The updated version have been placed in publicly available repositories.

Github

The source Code repository are saved @

https://github.com/DanielAdeniji/MSSQLServerDeadlocksSimulation/tree/master/ServerTrace

References

References – Trace State Management

References – Trace Profiling

References – Transact SQL (Auxiliary commands)

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s