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.
Does anyone have any info or opinions on this?
Cheers,
Reece.
|||We experienced the same frustration for deadlocks, primary key violations, login failures and permission denied. These alerts are needed on several of our SQL 2000 servers, but can no longer be implemented on SQL 2005. We find this very frustrating and do not understand the logic behind the decision to remove this functionality.
Dave
|||completely agree with you and I can't understand why this is so. I did try via the dedicated admin connection but still couldn't appear to get around this. I used to set alerts on various errors in sql 2000 for all types of errors as part of debugging production software releases and such. Not needed to do this for last couple of years but now want to do this with sql 2005 and I can't. What a pain!
|||I beleive you can use DBcc traceon to capture the details of deadlock victims details .DBCC TRACEON
Trace flag 1204
This trace flag returns the type of locks participating in a deadlock and the current command affected.
Trace flag 1205
This trace flag returns more detailed information about the command being executed at the time of a deadlock.
|||Yes I know about and use the trace flags - but what I really wanted was an alert on the deadlock so I could grab a snapshot of all actiivty, and know when the deadlock occurred without trawling the error log.
|||I'm glad I'm not the only one experiencing this frustrating change in functionality.
I have also listed this on the BETA site for 2008, as something that should be added back into the engine. But as yet I haven't had any feedback from Microsoft on whether it's going to make it on the to-do list or not.
We've also tried the trace flags, but I agree with colin, all we want is an alert that a deadlock has occurred, without the verbose logging that the trace flags bring with them.
Cheers.
No comments:
Post a Comment