How to capture and record deadlocks in SQL Server

Someone asked me how to capture and record the deadlock information on the Forum. Here, I will share some of my experiences with you, and I would like to ask you to correct them.

We know that you can use
SQL
The profiler tool provided by the server is used to track deadlock information. However, this method has a big flaw, that is, it consumes a lot. According to a foreign expert, profiler can even occupy servers.
The total bandwidth of the server is 35%. Therefore, it is obviously not a good idea to use profiler in a busy system. Below I will introduce two methods that consume less. The second type consumes the least
It can also be used in busy systems. The first type is the most flexible and can meet the needs of a variety of applications.

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.

For example
If you want to make sure that the flag is automatically enabled after the SQL Server is restarted, you can use/T in the SQL Server service startup option.
The START option specifies that the trail flag is set to on 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.