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.
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.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.