How to capture and record deadlocks that occur in SQL Server

Method One: Use SQL Server Agent (alert+job)
The specific steps are as follows:
1. First use the following command to enable the relevant trace flags.
SQL Code
DBCC Traceon (3605,1204,1222,-1)
Description:
3605 Output The DBCC results to the error log.
1204 returns the resource and type of the lock that participates in the deadlock, and the current command affected.
1222 returns the resources and types of locks participating in deadlocks, and the affected current commands that use XML formats that do not conform to any XSD schema (further than 1204, SQL 2005 and above are available).
-1 opens the specified trace token globally.
All of the above trace flag scopes are global, that is, during SQL Server running, it will work until SQL Server restarts.
If you want to make sure that SQL Server automatically opens these flags after restarting, you can use the/T startup option in the SQL Server service startup option to specify that the trace flag be set to open during startup. (located in SQL Server Configuration Manager->sql Server service->sql server-> Properties-> advanced-> startup parameters)
After running the above statement, when a deadlock occurs in SQL Server, it can already be seen in the error log but not intuitive (and other information is mixed). (SSMS-> SQL Server Instance-> admin-> SQL Server log)

2. Build the table, store the deadlock record
SQL Code
Use [Cole]--cole is my sample database, you can modify it according to the actual situation.
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, write a step name casually, the database is "Cole" (see 2), and in the "command" column, enter the following statement:
SQL Code
--New temporary table
IF object_id (' tempdb.dbo. #ErrorLog ') is not Null
DROP table #ErrorLog
C reate table #ErrorLog (Id int IDENTITY (1, 1) not NULL, a DATETIME, B VARCHAR (Ten), C VARCHAR (MAX))
-Inserts the current log record into the temporary table
insert INTO #ErrorLog EXEC master.dbo.sp_readerrorlog
-Insert deadlock information into user table
Insert Deadlocklog
Select a, B, C
From #ErrorLog
where ID >= (select MAX (ID) to #ErrorLog where C like '%deadlock encountered% ')
DROP TABLE #ErrorLog

4. New Alert
on the General tab of the new alert form, make the following settings:
Name: Can be named according to the actual, I use Deadlockalert
Type: Select SQL server performance condition Alert
Object: Sqlserver:locks
Counter: Number of Deadlocks/sec
instance: _total
The alert is triggered when the following conditions are true: higher than When the
Value: 0
setting is complete, it should look like the following illustration:

On the Response tab, select Execute Job and select our new job (that is, deadlockjob)
in step 3. So far, we've done all the steps, and then you can always query the Deadlocklog table to display the deadlock information.

Method Two: Use server-side tracing. The
implementation steps are as follows:
1. Write the following script and execute
SQL code
--Define parameters
Declare @rc int
Decla Re @TraceID int
Declare @maxfilesize bigint
Set @maxfilesize = 5
--Initialize trace
Exec @rc = sp_trace_create @ Traceid output, 0, N ' e:/dblog/deadlockdetect ', @maxfilesize, NULL
--The e:/dblog/deadlockdetect here is the filename (can be modified by itself), SQL will automatically add the. trc extension
if (@rc!= 0) Goto Error
--Set trace event
Declare @on bit
Set @on = 1
--148 of the following statements refer to Locks:deadlock Graph event (see sys.trace_events), 12 refers to the SPID column (see SYS.TRACE_COLUMNS)
exec sp_trace_setevent @TraceID, 148 , @on
exec sp_trace_setevent @TraceID, 148, one, @on
exec sp_trace_setevent @TraceID, 148, 4, @on
exec s P_trace_setevent @TraceID, 148, @on
exec sp_trace_setevent @TraceID, 148, num, @on
exec sp_trace_setevent @Tr Aceid, 148, @on
exec sp_trace_setevent @TraceID, 148, 1, @on
--Start tracing
exec sp_trace_setstatus @TraceID, 1
--rememberRecord the trace ID for later use
Select traceid = @TraceID
Goto finish
Error:
Select errorcode= @rc
Finish:
Go br> After you run the above statement, every time a deadlock event occurs in SQL Server, a record is automatically inserted into the file E:/DBLOG/DEADLOCKDETECT.TRC.

2. Pausing and stopping server-side tracing
to pause the above server-side trace, run the following statement:
SQL Code
EXEC sp_trace_setstatus 1, 0--The first parameter represents the Traceid, which is the output parameter in step 1. The second parameter indicates that the state is changed to 0, which is paused
If you want to stop the above server-side trace, run the following statement:
SQL Code
EXEC sp_trace_setstatus 1, 2--The first parameter represents the Traceid, which is the output parameter in step 1. The second parameter indicates that the state is changed to 2, that is, stop

3. View the contents of the trace file
for the trace file generated above (E:/DBLOG/DEADLOCKDETECT.TRC), there are two ways to view:
1). Execute T-SQL command
SQL Code
SELECT * from fn_trace_gettable (' E:/dblog/deadlockdetect.trc ', 1)

The TextData column in the result returns the details of the deadlock as XML.
2). Open in SQL Server Profiler.
in turn, enter the profiler-> open the trace file-> Select E:/dblog/deadlockdetect.trc, you can see the graphical display of deadlock information.