Deadlock Logging in SQL Server Error Log

Back to: SQL Server Tutorial For Beginners and Professionals

Deadlock Logging in SQL Server Error Log

In this article, I am going to discuss Deadlock Logging in SQL Server Error Log i.e. how to log the deadlock information to the SQL Server error log. Please read our previous article where we discussed Deadlock in SQL Server with Examples. As we already discussed in our previous article when deadlocks occur, the SQL Server chooses one of the transactions as the deadlock victim and rollback that transaction so that the other transaction can move forward.

How to Find Deadlock Queries in SQL Server?

There are many ways available in SQL Server to track down the queries which are causing the deadlocks. One of the options that is available in SQL Server is to use the SQL Server Trace Flag 1222 to log the deadlock information to the SQL Server Error Log. Let discuss how to enable the Trace Flag in SQL Server.

Enable Trace Flag in SQL Server: 

To enable the trace flag in SQL Server we need to use the DBCC command. The -1 parameter indicates that the trace flag must be set at the global level. If we omit the -1 parameter then the trace flag will be set only at the session-level. 

To enable the trace flag
DBCC Traceon(1222, -1) 

To check the status of the trace flag
DBCC TraceStatus(1222, -1) 

To disable the trace flag
DBCC Traceoff(1222, -1)

Example: Trace Flag in SQL Server

Let us understand how to enable and use Trace Flag in SQL Server with an example. We are going to use the following two tables to understand this concept.

Please use the below SQL Script to create and populate the tables with the test data.

-- Create table TableA
CREATE TABLE TableA
(
    ID INT,
    Name NVARCHAR(50)
)
Go

-- Insert some test data
INSERT INTO TableA values (101, 'Anurag')
INSERT INTO TableA values (102, 'Mohanty')
Go

-- Create table TableB
CREATE TABLE TableB
(
    ID INT,
    Name NVARCHAR(50)
)
Go

-- Insert some test data
INSERT INTO TableB values (1001, 'Priyanka')
INSERT INTO TableB values (1002, 'Dewagan')
Go

Now create two stored procedure as shown below.

-- Stored Procedure 1
CREATE PROCEDURE spTransaction1
AS
BEGIN
    BEGIN TRANSACTION
    UPDATE TableA SET Name = 'Anurag From Transaction 1' 
  WHERE Id = 101

    WAITFOR DELAY '00:00:10'

    UPDATE TableB SET Name = 'Priyanka From Transaction 2' 
  WHERE Id = 1001
    COMMIT TRANSACTION
END

-- Stored Procedure 2
CREATE PROCEDURE spTransaction2
AS
BEGIN
    BEGIN TRANSACTION
    UPDATE TableB SET Name = 'Priyanka From Transaction 2' 
  WHERE Id = 1001

    WAITFOR DELAY '00:00:10'

    UPDATE TableA SET Name = 'Anurag From Transaction 2' 
  WHERE Id = 101
    COMMIT TRANSACTION
END

First, enable the Trace Flag by executing the following command

DBCC Traceon(1222, -1) 

Then open 2 instances of SQL Server Management Studio. From the first instance execute the spTransaction1 stored procedure and from the second instance execute the spTransaction2 stored procedure. After a few seconds, you will notice that one of the transactions completes its execution successfully while the other one is chosen as the deadlock victim and rollback.

The information about this deadlock now should have been logged in SQL Server Error Log. To read the error log you need to use the sp_readerrorlog system stored procedure as shown below.

EXECUTE sp_readerrorlog 

In the next article, I am going to discuss how to read and understand the deadlock information that is logged in the SQL Server error log. Here, in this article, I try to explain the Deadlock Logging in the SQL Server Error Log. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.