A Better Way to Enable Email Alerts for Deadlocks

yellow envelope with email @ sign on black computer keyboardA while back I wrote about a way to enable email alerts for deadlock conditions with SQL Server. I outlined how, in order to set up these alerts, I ended up having to tweak SQL Server's internal sys.messages table via sp_altermessage—something I wasn't too excited about (given how I mentioned that I don't typically recommend doing so and that I felt this was a bit of a "gateway script").

Happily, Wayne Sheffield (Twitter/blog) saw my article, noticed that I was trying to use a hammer in an unnatural way, and recommended I use a gas-powered brad nailer instead. Or, in other words, Wayne was nice enough to email me with a much better and cleaner solution.

Wayne's solution is quite spiffy, doesn't require any modifications to system tables, and is drop-dead easy to implement. (Of course, I double-checked that the solution works as expected, without any hiccups or problems—which it does.) Listing 1 contains Wayne's ingenious script.

  1. -- Tested SQL 2005 - 2012.
  2. DECLARE @perfcond NVARCHAR(100);
  3. DECLARE @sqlversion TINYINT;
  4.  
  5. -- get the major version of sql running
  6. SELECT  @sqlversion = ca2.Ver
  7. FROM    (SELECT CONVERT(VARCHAR(20),
  8.                         SERVERPROPERTY('ProductVersion')) AS Ver) dt1
  9.         CROSS APPLY (SELECT CHARINDEX('.', dt1.Ver) AS Pos) ca1
  10.         CROSS APPLY (SELECT SUBSTRING(dt1.Ver, 1, ca1.Pos-1) AS Ver) ca2;
  11.  
  12. -- handle the performance condition depending on the version of sql running
  13. -- and whether this is a named instance or a default instance.
  14. SELECT  @perfcond =
  15.         CASE WHEN @sqlversion >= 11 THEN ''
  16.         ELSE ISNULL(N'MSSQL$' +
  17.                 CONVERT(sysname, SERVERPROPERTY('InstanceName')), N'SQLServer') + N':'
  18.         END +
  19.         N'Locks|Number of Deadlocks/sec|_Total|>|0';
  20.  
  21. EXEC msdb.dbo.sp_add_alert
  22.     @name=N'Deadlock Alert',
  23.     @message_id=0,
  24.     @severity=0,
  25.     @enabled=1,
  26.     @delay_between_responses=0,
  27.     @include_event_description_in=0,
  28.     @category_name=N'[Uncategorized]',
  29.     @performance_condition=@perfcond,
  30.     --@job_name=N'Job to run when a deadlock happens, if applicable'
  31.     -- or
  32.     @job_id=N'00000000-0000-0000-0000-000000000000'
  33. GO
  34.  
  35. EXEC msdb.dbo.sp_add_notification
  36.     @alert_name = N'Deadlock Alert',
  37.     @notification_method = 1, --email
  38.     @operator_name = N'General'; -- name of the operator to notify
  39. GO

Under the hood, this script uses a SQL Server performance condition to specify alert logic—which is pretty visible/obvious in the script. If you want a better way to see how Wayne tackled this issue, you can crack open the alert in SQL Server Management Studio (SSMS) and take a peek at it, as Figure 1 shows.

Using SQL Server Management Studio to View a Deadlock Alert

Please or Register to post comments.

What's Practical SQL Server?

Practical advice, insight, and help for core SQL Server considerations.

Contributors

Michael K. Campbell

Michael K. Campbell is a contributing editor for SQL Server Pro and Dev Pro and is an ASPInsider. Michael is the president of OverAchiever Productions, a consultancy dedicated to technical evangelism...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×