Reading deadlocks in SQLMonitor query

staggerlee011staggerlee011 Posts: 19 Bronze 1
edited October 3, 2012 6:58AM in SQL Monitor Previous Versions
Hi,

we have started getting deadlocks in an application and has brought up a couple of questions i was hoping someone could help with.

I can see traceflag 1204 is on which puts deadlock text into the event log. Did SQL Monitor enable the traceflag? Its possible someone else in the team did and didnt mention it.

In SQLMonitor i cant see the actual T-SQL for either process. Is it possible to get this from SQLMonitor?

If the SQLMonitor does use the traceflag 1204 is it possible to use the XML output instead 1222 and get the grapth put into sqlmon?

Thanks for any help

Regards
S

Comments

  • SQL Monitor does enable traceflag 1204 yes. As a monitoring product, we have a relatively low impact on servers but that trace flag is essential so that we can detect deadlocks.

    Regarding the T-SQL for the processes, it's possible from within SQL Monitor to enable a server-side trace. This is done via Configuration > Trace. After enabling this on the relevant server, future deadlock alerts should show trace information in the Performance Data section of the alert details page (in a tab named "SQL processes / Profiler trace"). Click the relevant SPID and trace should be displayed. Note that running the trace impacts the monitored server so it should ideally be disabled once you have seen enough information.

    We have an enhancement request (SRP-4176) to move from traceflag 1204 to 1222. 1204 is required for SQL Server 2000 support but we are considering the option of using 1222 on more recent SQL Server versions.

    Regards
    Chris
    Chris Spencer
    Test Engineer
    Red Gate
  • Hi Chris,

    Thanks for the quick reply..

    Regards
    S
Sign In or Register to comment.