How to capture and record deadlocks in SQL Server

Method 1: Use the SQL Server proxy (Alert + Job)
The procedure is as follows:
1. First, use the following command to enable the trace flag.
SQL code
Dbcc traceon (3605, 1204, 1222,-1)
Note:
3605 output the result of DBCC to the error log.
1204 return the resources and types of the locks involved in the deadlock and the affected current commands.
1222 return the resources and types of the locks involved in the deadlock, and the affected current commands using XML formats that do not conform to any XSD architecture (more advanced than 1204, SQL 2005 and above are available ).
-1 open the specified trace tag globally.
The scope of the above trace flag is global, that is, it will always play a role during SQL Server running until SQL Server restarts.
If you want to make sure that the tracing flag is automatically enabled after the SQL Server is restarted, you can use the/T startup option in the SQL Server service startup option to specify that the tracing flag is enabled during startup. (In SQL Server Configuration Manager> SQL Server service> SQL Server> Properties> advanced> startup parameters)
After running the preceding statement, you can see it in the error log when the SQL Server publishes a life-and-death lock, but it is not intuitive enough (mixed with other information ). (SSMS-> SQL Server instance-> Management-> SQL Server log)

2. Create a table to store deadlock records
SQL code
USE [Cole] -- Cole is my sample database. You can modify it as needed.
GO
Create table DeadLockLog (
Id int IDENTITY (1, 1) not null,
LogDate DATETIME,
ProcessInfo VARCHAR (10 ),
ErrorText VARCHAR (MAX)
)
GO

3. Create a JOB
Create a new JOB (assuming the name is DeadLockJob), create a new step in "Step", and enter a step name casually. The database is "Cole" (see Figure 2. in the "command" column, enter the following statement:
SQL code
-- Create a temporary table
IF OBJECT_ID ('tempdb. dbo. # errorlog') IS Not Null
Drop table # ErrorLog
Create table # ErrorLog (Id int IDENTITY (1, 1) not null, a DATETIME, B VARCHAR (10), c VARCHAR (MAX ))
-- Insert the current log record into a temporary table
Insert into # ErrorLog EXEC master. dbo. sp_readerrorlog
-- Insert the deadlock information into the user table
Insert DeadLockLog
Select a, B, c
From # ErrorLog
Where id> = (select MAX (id) from # ErrorLog WHERE c Like '% Deadlock encountered % ')
Drop table # ErrorLog

4. Create an alarm
On the "General" tab of the "Create alarm" form, perform the following settings:
Name: you can name it by yourself. Here we use DeadLockAlert
Type: Select "SQL Server performance condition alarm"
Object: SQLServer: Locks
Counter: Number of Deadlocks/sec
Instance: _ Total
An alarm is triggered when the counter meets the following conditions: higher
Value: 0
After the configuration is complete, it should be shown in:

On the "response" tab, select "execute job" and select the new job (DeadlockJob) in step 3)
So far, we have completed all the steps. Later, you can query the DeadLockLog table at any time to display the deadlock information.

Method 2: Use server-side tracking.
The specific implementation steps are as follows:
1. Write the following script and execute
SQL code
-- Define parameters
Declare @ rc int
Declare @ TraceID int
Declare @ maxfilesize bigint
Set @ maxfilesize = 5
-- Initialize a trail
Exec @ rc = sp_trace_create @ TraceID output, 0, N 'e:/DbLog/deadlockdetect ', @ maxfilesize, NULL
-- Here e:/dblog/deadlockdetect is the file name (which can be modified by yourself). SQL will automatically add the. trc extension to the end.
If (@ rc! = 0) goto error
-- Set the tracking event
Declare @ on bit
Set @ on = 1
-- In the following statement, 148 refers to the locks: deadlock graph event (see sys. trace_events), and 12 refers to the spid column (see sys. trace_columns)
Exec sp_trace_setevent @ TraceID, 148, 12, @ on
Exec sp_trace_setevent @ TraceID, 148, 11, @ on
Exec sp_trace_setevent @ TraceID, 148, 4, @ on
Exec sp_trace_setevent @ TraceID, 148, 14, @ on
Exec sp_trace_setevent @ TraceID, 148, 26, @ on
Exec sp_trace_setevent @ TraceID, 148, 64, @ on
Exec sp_trace_setevent @ trace ID, 148, 1, @ on
-- Start a trail
Exec sp_trace_setstatus @ TraceID, 1
-- Record the trace ID for later use
Select TraceID = @ TraceID
Goto finish
Error:
Select ErrorCode = @ rc
Finish:
Go
After the preceding statement is run, a record is automatically inserted into file e:/DbLog/deadlockdetect. trc every time a lifecycle lock event occurs in SQL Server.

2. Pause and stop server tracking
If you want to pause the above server Trace, you can run the following statement:
SQL code
Exec sp_trace_setstatus 1, 0 -- the first parameter indicates TraceID, that is, the output parameter in step 1. The second parameter indicates changing the status to 0, that is, suspending
If you want to stop the above server trace, run the following statement:
SQL code
Exec sp_trace_setstatus 1, 2 -- the first parameter indicates TraceID, that is, the output parameter in step 1. The second parameter indicates changing the status to 2, that is, stopping

3. view the tracking File Content
The preceding trace file (e:/DbLog/deadlockdetect. trc) can be viewed in two ways:
1). Execute the t-SQL command
SQL code
Select * from fn_trace_gettable ('e:/DbLog/deadlockdetect. trc', 1)

The TextData column in The result returns detailed information about the deadlock in XML format.
2) open it in SQL Server Profiler.
Choose Profiler> open Tracing file> e:/DbLog/deadlockdetect. trc to view the deadlock information displayed in graphs.