Deadlock Alert - Not enough information provided

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.

Comments

  • 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.
  • Hi

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

    Regards
    Chris
    Chris Spencer
    Test Engineer
    Red Gate
  • 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
    Chris Spencer
    Test Engineer
    Red Gate
  • Just out of interest why are you using 1204 and not 1222? Could it possibly be a user option?
  • 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
    Chris Spencer
    Test Engineer
    Red Gate
  • 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.
  • oderksoderks Posts: 67 Bronze 2
    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.
Sign In or Register to comment.