Deadlock Alert (message ID 1205) No Longer Able To Be Logged In 2005

Hi all,

In SQL Server 2000 you could run the piece of code below, to enable the logging of a deadlock in the SQL Server error log. Which could then be used to fire an alert, and then kick of an Agent job to send an SMTP email alert.

Exec sp_altermessage 1205, 'WITH_LOG', 'true'


The error message logged was a nice simple one liner, like this:

Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Now I work for a managed SQL Server company, and a large number of our clients used our alerting for deadlocking to tune their applications, or at a minimum to show them when something was wrong with the database due to sudden rise in the number of deadlocks.

However, in SQL Server 2005, the functionality for the sp_alertmessage procedure has been changed so that you can't update any message id less than 50,000. Which comes inline with the secure engine that Microsoft have designed.

But this now means you can no longer enable the logging for deadlock message ID 1205. Which in turn means no alerting can be enabled.

You can still log information by enabling the necessary trace flags, however that logs very verbose information about the deadlocking chain, which in turn can quickly blow the size of the error logs out.

What I would love to see is this functionality returned in SQL Server 2008, or at least an alternative so that only minimum information is logged initially for a deadlock, and alerting can be setup.

Also, for those of you who have read through the 2005 BOL, about deadlocking, although it states the following in the section on deadlocking:
"...The 1205 deadlock victim error records information about the threads and resources involved in a deadlock in the error log.€?

This isn't the case, unless you enable some trace flags, which as mentioned will give you a whole lot of information, which although is valuable, isn't ideal if you're wanting day to day deadlock tracking.

Does anyone have any thoughts on this? Have you struck this as well? Do you think this should be something that shouldn't have been removed from 2000?

Cheers,
Reece.