For database dead locks, you can usually pass the Trace Flag 1204, 1205, 1206, the output inside ERRORLOG, and analysis of the execution context of SQLTrace to determine the cause of the deadlock problem.
The third parameter of the Traceon function is set to -1, indicating that it is not only for the current Connection, but for all Connection including future creation. In this way, it is enough, otherwise it is simply monitored the currently established database connection.
Execute the deadlock to ErrorLog in order to do it:
DBCC TRACEON (1204, 3605, -1) godbcc tracestatus (-1) GO
The resulting output is:
DBCC is executed. If DBCC outputs an error message, contact your system administrator. Traceflag Status -------------- 1204 11205 13605 1
(The number of rows affects is 3 lines)
DBCC is executed. If DBCC outputs an error message, contact your system administrator.
Since then, you can view the routine log of the database. Every time, the database will check the deadlock, the log text is as follows:
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: 55 ECID: 0 EC: (0xAA577570) VALUE: 0x
4C
25cba02004-01-16 18: 34: 38.50 SPID4 Node: 1 restaurant: LOCKOWNER STYPE: 'OR' MODE: U SPID: 55 ECID: 0 EC: (0xAA577570) VALUE: 0X
4C
25cba02004-01-16 18: 34: 38.50 SPID4 Node: 2 Restype: LOCKOWNER Stype: 'OR' Mode: U SPID: 71 ECID: 0 EC: (0xAbf07570) Value: 0x9bd0ba002004-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 ECID: 0 EC :( 0xABF07570) Value: 0x9bd0ba002004-01-16 18: 34: 38.50 SPID4 2004-01-16 18: 34: 38.50 SPID4 2004-01-16 18: 34: 38.50 SPID4 Endlock Search 1976 ... a deadlock was not found. 2004-01-16 18: 34: 38.50 SPID4 ----------------------------------
Appendix: DBCC Traceon opens (enabled) the specified tracking tag.
Note Tracking tags Used to customize the characteristics of certain ways to control Microsoft® SQL Server? Track tags remain enabled in the server until it is disabled by executing the DBCC Traceoff statement. Before issuing DBCC Traceon statements, the new connection to the server can not see any tracking tags. Once this statement is issued, the connection can see all tracking tags currently enabled in the server (even if these tags are enabled by other connections). Track tag tracking tags are used to temporarily set specific features or close specific behaviors. If you set the tracking mark 3205 when you start Microsoft® SQL Server, the hardware compression of the tape driver will be disabled. Track tags are often used to diagnose performance issues or debug stored procedures or complex computer systems.
The following tracking marks are available in SQL Server.
Track tag
description
1204 Returns the type of locks involved in the deadlock and the current affected command.
In fact, you can "error 1000
-1999
"
Find them:
120419SQL Server cannot obtain the Lock resource at this time. Run your statement when the number of active users is small, or request the system administrator to check the SQL Server lock and memory configuration. 120513 Transaction (Process ID% 1!) And another process has been deadlocked on resources {% 2!}, And the transaction has been selected as deadlock victim. Please re-run the transaction. 120618 Transaction Manager has canceled distributed transactions.
