I have configured an alert in Management Studio for Deadlocks (error code 1205).
But when I forced a deadlock to occur, the Alert didn't trigger, no email received.
Do you have to have Trace flags 1204 and 1222 ON for messages to be written to SQL Logs when a transaction deadlock occurs?
Thanks
If you're not getting email, verify that you've configured Database Mail, assigned a default operator, and that database mail is running and tested sending email successfully. Also make sure the alert has the notification set to a valid email operator (sometimes the GUI doesn't set it).
If you are running SQL Server 2008 or higher, you can refer to the default trace or the system_health extended event to get deadlock information:
Neither of these require trace flags. The trace flags only affect the logging of deadlock information in the SQL error log, which is typically not the best output to read. Reading the system_health event may require some XML skills, but it's usually not that difficult. I highly recommend using extended events rather than trying to parse the error log output.
Jonathan Kehayias has some good information on parsing deadlock XML from extended events:
Can you post the actual alert definition?
I can script it out tomorrow, Scott but it's just configured to Alert on Error 1205.
Tomorrow I will enable trace flags 1204 and 1222. I don't know if that will help or not.
Thanks for your interest.
That helps. I don't reference a specific error # at all. I use this condition in my alert, and I've never noticed any missed alerts:
@performance_condition=N'MSSQL$<instance_name>:Locks|Number of Deadlocks/sec|_Total|>|0',
Naturally I've removed my actual instance name, but otherwise it's exactly as I specify it in the Alert condition.
1 Like
Thanks Scott.
So I've configured it with:
Type: SQL Server Event Alert
Severity: 010 - Information ** What should this be set to ?
Raise Alert when message contains: @performance_condition=N'MSSQL$MSSQLSERVER:Locks|Number of Deadlocks/sec|_Total|>|0'
How does this look? And yes DB mail is configured correctly, it works
Thanks
I have the Alert start a job using that performance condition alert. Again, I don't rely on a SQL message containing a certain string / string pattern.
1 Like
ScottPletcher, your tip worked perfectly. And, we have already had a couple of deadlocks today to prove it. The Alert works and sends the email notification. Thanks very much for your help.
Great! I like using a job because the job can then do multiple things. For example, I log into deadlock into a table, with an identity, so that I have a unique id for every deadlock.
The job can use the views you mentioned, etc., to get more details on the deadlock and provide them to you.