Start deadlock checking for SQL Server

Server|sqlserver for database deadlocks, it is usually possible to determine the origin of the deadlock problem through trace FLAG 1204, 1205, 1206, checking the output inside the errorlog, and analyzing the SQLTrace execution context.
The third parameter of the Traceon function is set to-1, which means not only for the current connection, but for all, including future builds
of connection. This is sufficient, otherwise it is just monitoring the database connection that is currently established.



Execute the following to record the deadlock in the errorlog:

DBCC TRACEON (1204, 3605,-1)
Go
DBCC TRACESTATUS (-1)
Go





The resulting output is:

DBCC execution completed. If DBCC prints an error message, contact your system administrator.
Traceflag Status
--------- ------
1204 1
1205 1
3605 1

(The number of rows affected is 3 rows)

DBCC execution completed. If DBCC prints an error message, contact your system administrator.













After that, you can view the database's routine log, and every once in a while, the database checks for deadlocks, and the log text looks like this:

2004-01-16 18:34:38.50 Spid4----------------------------------
2004-01-16 18:34:38.50 Spid4 starting deadlock Search 1976



2004-01-16 18:34:38.50 spid4 Target Resource Owner:
2004-01-16 18:34:38.50 spid4 restype:lockowner stype: ' OR ' mode:u spid:55 Ec: (0xaa577570) value:0x4c25cba0
2004-01-16 18:34:38.50 spid4 node:1 restype:lockowner stype: ' OR ' mode:u spid:55 Ec: (0xaa577570) value:0x4c25cba0
2004-01-16 18:34:38.50 spid4 node:2 restype:lockowner stype: ' OR ' mode:u spid:71 Ec: (0xabf07570) value:0x9bd0ba00
2004-01-16 18:34:38.50 Spid4
2004-01-16 18:34:38.50 Spid4--Next branch--
2004-01-16 18:34:38.50 spid4 node:2 restype:lockowner stype: ' OR ' mode:u spid:71 Ec: (0xabf07570) value:0x9bd0ba00
2004-01-16 18:34:38.50 Spid4
2004-01-16 18:34:38.50 Spid4
2004-01-16 18:34:38.50 Spid4 End Deadlock Search 1976. A deadlock is not found.
2004-01-16 18:34:38.50 Spid4----------------------------------




















Appendix:
DBCC Traceon
Opens (enables) the specified trace token.



Comments
Trace flags are used to customize some control Microsoft®sql Server? The characteristics of the mode of operation. Trace flags are in the server
Remains enabled until it is disabled by executing the DBCC traceoff statement. In the issue of DBCC Traceon
Statement, a new connection to the server does not see any trace flags. Once the statement is issued, the connection can see the service
All trace flags that are currently enabled in the device, even if they are enabled by another connection.

Trace flag
Trace flags are used to temporarily set specific characteristics of the server or to turn off specific behavior. If you start Microsoft®sql Server
When tracking flag 3205 is set, hardware compression for the tape driver is disabled. Trace tags are often used to diagnose performance problems.
Or debug a stored procedure or a complex computer system.

The following trace flags are available in SQL Server.

Trace flag

Describe

160°

Returns the type of lock that participates in the deadlock and the currently affected command.



You can actually find them in "Error 1000-1999":



160°

19

SQL Server cannot get the LOCK resource at this time. Rerun your statement when the number of active users is low, or ask your system administrator to check for SQL Server locks and memory configurations.

1205

13

The transaction (process ID%1!) and another process have been deadlocked on the resource {%2!} and the transaction has been selected as a deadlock victim. Please run the transaction again.

1206

18

The transaction manager has canceled the distributed transaction.