How to Read and Analyze Deadlock Logs in SQL Server

It is a well-known fact that deadlocks cannot be avoided. It is necessary that we learn to minimize deadlocks from our SQL server environment. Trace flags enable you to capture deadlock graph in the SQL Server Error log. In this article, we will learn how to get the deadlock history from that log.

Deadlock

DeaDeadlockdlock occurs when there is more than one session competing for a resource. When a deadlock occurs, SQL Server immediately jumps into action by killing one of the session and that session would be tagged as a victim. Though a SQL Server Profiler can help you to capture the deadlock graph, the profiler should be running when a deadlock happens. The easy option is to set the deadlock trace flag and capture the deadlock session in SQL Server Error log. Deadlock is very common on a busy SQL server. However, the frequency of such deadlocks should be monitored.

How this works

SQL Server Error Log With DeadlockIn this script, we would be storing server names in a table. The command to read the SQL Server Error log would then be dynamically generated and executed against each linked server. The collected data is stored in a log table. Please note, all servers added in this scripts should be a linked server or else the script cannot read the Error Log

DECLARE @Tserver TABLE 
     ( 
         cserver VARCHAR(200) 
     )

INSERT INTO @Tserver 
VALUES ('SERVER1')

DECLARE @Tcmd TABLE 
     ( 
         ccmd VARCHAR(2000) 
     ) 
DECLARE @Tlog TABLE 
     ( 
         cdate DATETIME, 
         cinfo VARCHAR(200), 
         cmessage VARCHAR(2000) 
     )

INSERT INTO @Tcmd 
SELECT 'exec (''EXEC sp_readerrorlog 0,1,''''was deadlocked'''''') at [' 
              + cserver + ']' 
FROM @Tserver

DECLARE c1 CURSOR FOR 
     SELECT * 
     FROM @Tcmd 
DECLARE @cmd VARCHAR(2000)

OPEN c1

FETCH next FROM c1 INTO @cmd

WHILE @@FETCH_STATUS = 0 
     BEGIN 
         PRINT @cmd

         INSERT INTO @Tlog 
                     (cdate, 
                      cinfo, 
                      cmessage) 
         EXEC(@cmd)

        FETCH next FROM c1 INTO @cmd 
     END

CLOSE c1

DEALLOCATE c1

SELECT * 
FROM @Tlog

Reference table

In the script, the text ‘was deadlocked’ is used as a parameter. However, we can change it as per our requirement. The following table displays some of the messages that we would see in a SQL Server Error log after a deadlock occurs.

Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Object ID %ld (object ‘%.*ls’): A deadlock occurred while trying to lock this object for checking. This object has been skipped and will not be processed.
All schedulers on Node %d appear deadlocked due to a large number of worker threads waiting on %ls. Process Utilization %d%%.
Distribution agent encountered a deadlock while applying commands in SubscriptionStreams mode.  Spid %u waiting on lock (type ‘%s’, mode ‘%s’) for index ‘%s’ of table ‘%s’.
Replication transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. The operation will be retried.

If we are saving the log table for a longer period, it’s a better idea to take a backup of the database and delete old records. This will save a lot of time and energy for us when there is a SQL Server corruption.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Outlook repair and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.