Deadlocks can be a common issue in SQL Server, causing performance problems and impacting the overall stability of the system. In this article, we will discuss how to detect and notify deadlocks in SQL Server.

The Problem

A client recently contacted me because they were experiencing deadlocks in their SQL Server 2005 database. They wanted to be notified via email whenever a deadlock occurred. However, the usual method of using the sp_altermessage stored procedure was not working in SQL Server 2005.

In SQL Server 2005, you can only run sp_altermessage against messages with IDs greater than or equal to 50,000. This limitation prevents us from associating a system message with an alert that does not have the WITH_LOG option specified, resulting in the alert not firing.

The Workaround

To overcome this limitation, I have developed a workaround that utilizes Database Mail and SQL Server Agent. Here are the steps to implement this workaround:

1. Make sure Database Mail is configured in your SQL Server instance.
2. Ensure that SQL Server Agent has the correct Alert System Configurations set to utilize this method.
3. Enable Traceflag 1222 by running the command DBCC TRACEON(1222, -1).
4. Create a stored procedure that performs the following steps:

IF OBJECT_ID('dbo.usp_DeadlockNotification') IS NOT NULL
   DROP PROC dbo.usp_DeadlockNotification

CREATE PROC dbo.usp_DeadlockNotification
   @FilterBIT = 0,
   @Minutes INT = 30
AS
DECLARE @ErrorLog TABLE 
(
  LogDate DATETIME NOT NULL,
  ProcessInfo  VARCHAR(75),
  LogInfo VARCHAR(MAX)
)

DECLARE @Count INT,
        @StartDate DATETIME,
        @EndDate DATETIME

SET @Count = 0
SET NOCOUNT ON

-- Step I: Import Errorlog
INSERT INTO @Errorlog
  EXEC xp_readerrorlog 

-- Step II: How to search Errorlog
IF (@Filter <> 0)
BEGIN
   SELECT @EndDate = GETDATE()
   SELECT @StartDate = DATEADD(mi, -@Minutes, @EndDate)
   SELECT @Count = COUNT(*) 
   FROM @Errorlog
   WHERE LogDate BETWEEN @StartDate AND @EndDate
   AND LogInfo LIKE '%Deadlock%'
END
ELSE
BEGIN
   SELECT @Count = COUNT(*) 
   FROM @Errorlog
   WHERE LogInfo LIKE '%Deadlock%'
END

-- Step III: Send Email
IF (@Count > 0)
BEGIN
   EXEC msdb.dbo.sp_send_dbmail
   @profile_name = 'SQLTestCluster',
   @recipients   = 'pleblanc@lamar.com',
   @subject      = 'Deadlocks',
   @body = 'Please check errorlog for Deadlocks'
END

Once you have created the stored procedure, create a SQL Job that runs at intervals during the day when you suspect deadlocks may occur. Add a step to the job that executes the stored procedure.

For example:

EXEC dbo.usp_DeadlockNotification 1, 60

Schedule the job to run as needed.

Alternative Method

Another possible method of detecting deadlocks programmatically is to use the sys.dm_os_performance_counters dynamic management view. This method involves replacing the previous stored procedure with the following:

CREATE PROC dbo.usp_DeadlockNotification
AS
DECLARE @NumOfDeadLocks int

SELECT @NumOfDeadLocks = SUM(cntr_value)
FROM sys.dm_os_performance_counters 
WHERE counter_name LIKE '%dead%'

SET NOCOUNT ON

IF (@NumOfDeadLocks > 0)
BEGIN
  EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'SQLTestCluster',
  @recipients = 'pleblanc@tsqlscripts.com',
  @subject = 'Deadlocks',
  @body = 'Please check errorlog for Deadlocks'
END

However, it is important to note that the behavior of the sys.dm_os_performance_counters dynamic management view is still being researched. The data may be reset or refreshed at certain intervals, which could result in unwanted alerts.

Conclusion

By implementing the workaround described in this article, you can detect and notify deadlocks in your SQL Server database. The client in this example was able to receive notifications every 30 minutes and take appropriate actions to resolve the deadlocks.

If you have any questions, concerns, comments, or suggestions regarding this article, please feel free to email me at pleblanc@tsqlscripts.com.