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 the following articles before proceeding to this article.

https://dotnettutorials.net/lesson/sql-server-deadlock-examples/ – Here we discussed the basics of SQL Server Deadlock with an example.

https://dotnettutorials.net/lesson/sql-server-deadlock-victim-selection/ – Here we discussed the Deadlock Victim Selection Criteria used by SQL Server.

As we already discussed in our previous articles 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.

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: 

To enable the trace flag 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)

Let’s us understand this with an example.

We are going to use the following two tables in this demo.

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.