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 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
In 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
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.
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