Reading deadlocks in SQLMonitor query

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

Reading deadlocks in SQLMonitor query

Postby staggerlee011 » Wed Oct 03, 2012 10:24 am

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
staggerlee011
 
Posts: 9
Joined: Tue Jan 17, 2012 5:59 pm

Postby Chris Spencer » Wed Oct 03, 2012 11:49 am

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
Chris Spencer
 
Posts: 301
Joined: Tue Aug 29, 2006 9:09 am
Location: Red Gate - Cambridge

Postby staggerlee011 » Wed Oct 03, 2012 11:58 am

Hi Chris,

Thanks for the quick reply..

Regards
S
staggerlee011
 
Posts: 9
Joined: Tue Jan 17, 2012 5:59 pm


Return to SQL Monitor 3

Who is online

Users browsing this forum: No registered users and 0 guests