Re: URGENT deadlock question

From: ChrisR (bla_at_noemail.com)
Date: 12/17/04 

Date: Fri, 17 Dec 2004 11:38:30 -0800

Thanks for the quick response. However, now Im really confused.

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.

Will I get everything if I save to a table as well, or only a file?

Also, Ive always read that it would hurt performance to run Profiler on the
local box. But if Im reading correctly, you are saying thats the only way to
gauantee seeing all activities?
Do you know if I can get the needed info from the messages in the Error Log?

"David Gugick" <davidg-nospam@imceda.com> wrote in message
news:ezkZQwG5EHA.3368@TK2MSFTNGP10.phx.gbl...
> 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: Running profiler on busy 8-cpu Sql Server
    ... I always start with a light trace and add more detail as I go along to ... SQL Server MVP ... > I think it's also worth saying that you should introduce Profiler ... > SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Running profiler on busy 8-cpu Sql Server
    ... * I regularly run Profiler on VERy busy servers. ... where customers have told me 'theres no way to run Profiler, our server is ... SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: URGENT deadlock question
    ... >> 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 ... Just make sure the trace file is created on a local ...
    (microsoft.public.sqlserver.server)
  • Re: User and Login auditing
    ... You could use server side traces to trace the same things ... instead of running Profiler. ... You could put triggers on your trace table. ... How to Trace in SQL Server by Using Extended Stored ...
    (microsoft.public.sqlserver.security)
  • Re: Running profiler on busy 8-cpu Sql Server
    ... I also regularly use it on VERY busy servers nearly always without ... I think it's also worth saying that you should introduce Profiler gradually ... SQL Server MVP ... > SQL Server MVP ...
    (microsoft.public.sqlserver.server)