The PremierOne DBA team may request SQL Trace Flags be enabled in order to capture additional information when deadlocks occur.

You may also see examples of deadlocks recorded in the SQL Logs or one of the PremierOne Service Logs.

What is a SQL Deadlock?

Deadlock definition in SQL Server

In terms of SQL Server, a deadlock occurs when two (or more) processes lock the separate resource. Under these circumstances, each process cannot continue and begins to wait for others to release the resource. However, the SQL engine understands that this contention would never end with the help of the lock manager warning and then it decides to kill one process to solve this conflict problem so that the other process can be completed. The killed process is called the deadlock victim.

Here is an example of a deadlock reported by the CADS service log

2024-10-21 09:38:55,455 [125] MAJOR pswgs.cad.server.unitmanagement 
ErrorCode: 290781251718086656
Message: An error occurred updating the ARL location
Microsoft SQL Server 14.00.3471
MSSQLServer 1205 20476
SQL Exception:
Command Text:
procUnitUpdateLocationForARL_33_1
Parameter List:
UnitKey : ed554887-11b5-4947-bb1f-f00a46eef00a 
@ARLLongitude : -76.1583886666667
@ARLLatitude : 36.8441941666667
@ARLAltitude : 0
@ARLSpeed : 10.63407
@ARLHeading : 285.51
@ARLFlag : True
@ARLLocDeterminationPriority : 0
@ARLLocDeterminationResourceID : PD|CVB-587556
@ARLUpdateTime : 10/21/2024 1:38:51 PM
@ARLStatus : 1
@LastValidLocationTime : 10/21/2024 1:38:51 PM

System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 411) was *deadlocked on lock resources with another process and has been chosen as the deadlock victim*. Rerun the transaction.

In this example the referenced stored procedure, procUnitUpdateLocationForARL_33_1, is attempting to update table  UnifiedCAD.dbo.UM_Unit but is unable to at random times.

Microsoft SQL Server all versions

Enable SQL Trace Flags 1204, 1222

Trace Flag 1204: Focuses on the nodes involved in the deadlock. Each node has a dedicated section, and the final section describes the deadlock victim. 

Trace Flag 1222: Returns information in an XML-like format that does not conform to an XML Schema Definition (XSD) schema. 

The following needs to be performed on both PCADDB01 and PCADDB02 in order to enable the SQL flags and allow the capturing of additional data. 

Using SSMS; Execute the following commands to enable the flags
DBCC TRACEON (1204, -1);
GO

DBCC TRACEON (1222, -1);
GO

Example illustrating response message after executing the above commands.

Using SSMS; Execute the following command to verify they are enabled
DBCC TRACESTATUS()
GO

Example illustrating Trace Flags ARE NOT enabled.

Example illustrating Trace Flags ARE enabled

SQL Server DOES NOT need to be restarted for these flags to be enabled, however, if SQL Server is restarted for any purpose these flags will be removed by default.

To make sure the flags get re-enabled in the event SQL server is restarted the following needs to be added to the "Startup Parameters" tab for SQL  Server (PREMIERONE)

-t1204
-t1222

Launch SQL Server configuration Manager

Once enabled, these flags will log additional details on deadlocks to the SQL Server Logs.