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.
In the next article, we will discuss how to read and understand the deadlock information that is logged in the SQL Server error log.
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.