Re: URGENT deadlock question

From: David Gugick (davidg-nospam_at_imceda.com)
Date: 12/17/04 

Date: Fri, 17 Dec 2004 14:10:27 -0500

ChrisR wrote:
> I received 4 deadlocks in about 15 minutes. 3 of them occured at the
> same time. Im pretty sure that one was due to a large program that
> manipulates/ bcp's data in. That one is dealable. So I turned on
> Profiler. I have a deadlock template that I use with the following
> settings:
>
> Locks:
> DeadLock
> Deadlock Chain
>
> SP's:
> RPC Completed
> -- Starting
> SP Completed
> -- Starting
>
> TSQL:
> SQL: BatchCompleted
> -- BatchStarting
> SQL: Statement Completed
> SQL: Statement Starting
>
> According to BOL for DBCC TraceON(1204):
>
> "Trace flags remain enabled in the server until disabled by executing
> a DBCC TRACEOFF statement."
> I set DBCC TraceON(1204) a month or so ago so I assumed it would
> still be on. No reboots since then.
>
> So, Profiler is running and another Deadlock occurs 10 minutes later.
> I stop Profiler but there is no Deadlock that I can find. Throuhgtout
> the Trace, there is a message that says:
>
> "Some trace events have not been reported to SQL Profiler because the
> server has reached its maximum amount of available memory for the
> process."
>
> So, one of two things is happening:
>
> 1. The above message kept Profiler from reporting my Deadlock.
> 2. 1204 wasnt on.
>
> More info:
>
> In my SQL Error Log, there are about 30 lines of info per deadlock.
> They have various Stored Procedure and Select statements for the
> viewing. Ive been told that Profiler MUST be running when a deadlock
> occurs in order to track it down.
>
> So my questions are:
>
> 1. Does 1204 need to be set every time Profiler is started?
> 2. Cant I figure out the cause of my deadlocks based on the info from
> the Error Log? Is it correct that the info from the Error Log Cant be
> used to track down a deadlock?
>
> Any other pointers on the matter would be great.

You don't need the trace flag to see deadlock information in Profiler.
But it sounds like you really should be using a server-side trace and
not a Profiler started trace. Even using Profiler to start a server-side
trace actually creates two traces (on server-side and one for Profiler).
So start the server-side trace using T-SQL code you can generate from
Profiler using the Script Trace option on the File menu. When you use
Profiler to view a trace, SQL Server dumps rowsets across the network to
the Profiler client, but there is no guarantee you'll see everything if
SQL Server doesn't feel like it can keep up with all the activity. A
server-side trace to a trace file guarantees you'll see everything. Just
make sure the trace file is created on a local server drive, not on a
network drive or you'll throttle server SQL Server and kill overall
performance.

Instead of the Completed events, use the Starting ones for deadlock
detection, since a statement may not complete if its terminated. Use SQL
StmtStarting, RPC Starting, and SP StmtStarting if you know these
problems are occurring from within stored procedures. Obviously add the
deadlock and deadlock chain events. If you cam, filter the information
to limit data collection (if you know the users or applications
involved, for example).

If you look for the SQL Statement that was executed immediately before
the deadlock chain event for that SPID, you should see the SQL Statement
that was involved in the deadlock. It's a good idea to gather all
statements, not just the deadlock vitim to see all the SQL involved. SQL
Server 2000 does not report SPID information correctly in the deadlock
events, so always check the TextData column for these events for the
"real" SPID. 

-- 
David Gugick
Imceda Software
www.imceda.com

Relevant Pages

  • Re: User and Login auditing
    ... I agree 100% with Sue on not touching the system tables. ... > and hope that your employer doesn't mind you putting the SQL ... > Server box in this position. ... You could put triggers on your trace table. ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Profiler / DBCC Traceon (1204)
    ... you could have a SQL Trace running (the server side of what SQL ... > detail information on each deadlock. ...
    (microsoft.public.sqlserver.server)
  • Re: Problem with SQL Server [Solution]
    ... Apparently my sql server was not set to mixed mode authentication. ... > with the Trace Properties. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SQL 7 Traces
    ... Profiler 2K can generate the xp calls for a defined trace in 7 format. ... Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Analyzing Error log with Trace Flag 1204 turned on
    ... I have read the appropriate section out of Inside SQL ... up to a deadlock only from the spids involved in the deadlock? ... > resources in Inside SQL Server 2000, and in my ebook on Troubleshooting ... Are they locks currently held or ...
    (microsoft.public.sqlserver.server)