Deadlock Alert - Not enough information provided

Alexander_Webber

On Long Running Query or Blocked Process alerts you get complete SQL query fragments, but with Deadlock alerts you only get the first X amount of characters which is pretty useless in most cases.

Alexander_Webber

I was just wondering if you had any timelines for improving deadlock alerts as per my initial piece of feedback as we're thinking of buying 5 licences.

Chris Spencer

Hi

I currently investigating this issue and will hopefully be able to update you later today with some details.

Regards
Chris

Chris Spencer

Hi

This is almost certainly a limitation of the trace flag we are currently using (1204) and the amount of information available in the SQL Server error logs.

We already have an enhancement request raised (ref: SRP-3080) internally to review our current implementation. This would probably be done for the v3.0 release for which we have no timescales at the moment sorry.

Hope this helps
Chris

Alexander_Webber

Just out of interest why are you using 1204 and not 1222? Could it possibly be a user option?

Chris Spencer

The reason we use the 1204 trace flag is because it is supported on SQL Server 2000 as well as 2005/2008. Whilst there were probably very good technical reasons for this at the time, we are definitely planning to "upgrade" to the 1222 flag.

I can imagine that the trace flag we use would be automatic depending on the version of SQL Server, but I have added your suggestion of having a user option to the enhancement request.

Thanks again
Chris

Alexander_Webber

Thanks for the reply. Also it would be nice if you did some deciphering of the deadlock graph, taking into account what the deadlock is actually on. For example if it's deadlocking on a key-range then attempting to look at the virtual column %%LockRes%% and outputting the range.

oderks

I'd like to join up on the request to upgrade to 1222 where possible. Or as stated: useroption or based on sql version is even better.