traceon
I need to get detailed info on whatever may be causing deadlocks. i have seen many different iterations of the TRACEON statement;
DBCC TRACEON(1204,3605)
DBCC TRACEON(-1, 1204, 3605)
DBCC TRACEON (3605,1204,-1)
which is it? and its corresponding TRACEOFF?
also, i can do this w/out restarting sql, correct?
DBCC TRACEON(1204,3605)
DBCC TRACEON(-1, 1204, 3605)
DBCC TRACEON (3605,1204,-1)
which is it? and its corresponding TRACEOFF?
also, i can do this w/out restarting sql, correct?
ASKER
yes, aneesh, i actually have that opened up already. i'm just double-checking, i guess. no restart necessary, right?
and you don't know where I can find a list of the different trace flags, do you?
i've only found 260,1204,2528 and 3205 in BOL
and you don't know where I can find a list of the different trace flags, do you?
i've only found 260,1204,2528 and 3205 in BOL
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
dbaSQL,
> where I can find a list of the different trace flags, do you?
http://www.extremeexperts.com/SQL/Articles/DBCCTraceFlags.aspx
http://www.sqlservercentral.com/columnists/RDyess/traceflags.asp
> where I can find a list of the different trace flags, do you?
http://www.extremeexperts.com/SQL/Articles/DBCCTraceFlags.aspx
http://www.sqlservercentral.com/columnists/RDyess/traceflags.asp
ASKER
oh, i didn't see that in there anywhere, aneesh. so, trace on, restart, traceoff restart? and what of the overhead? is it really atrocious, or is it just specific to the deadlocks, if and when they occur?
Consider turning on SQL Server deadlock tracing in order to track deadlocks, when you are having deadlock-related issues. The overhead for doing this is minimal.
DBCC TRACEON (3605,1204,-1)
Once this is run, then all deadlocking activity will be written to the SQL Server log file. [7.0, 2000] Added 11-22-2004
*****
To help identify which tables or stored procedures are causing deadlock problems, turn on trace flag 1204 (outputs basic trace data) or trace flag 1205 (outputs more detailed trace data). Be sure to turn off this trace flag when you are done, as this trace can eat up SQL Server's resources unnecessarily, hurting performance. [6.5, 7.0, 2000] Updated 10-29-2004
Consider turning on SQL Server deadlock tracing in order to track deadlocks, when you are having deadlock-related issues. The overhead for doing this is minimal.
DBCC TRACEON (3605,1204,-1)
Once this is run, then all deadlocking activity will be written to the SQL Server log file. [7.0, 2000] Added 11-22-2004
*****
To help identify which tables or stored procedures are causing deadlock problems, turn on trace flag 1204 (outputs basic trace data) or trace flag 1205 (outputs more detailed trace data). Be sure to turn off this trace flag when you are done, as this trace can eat up SQL Server's resources unnecessarily, hurting performance. [6.5, 7.0, 2000] Updated 10-29-2004
dbaSQL,
> so, trace on, restart, traceoff restart?
Yes,,
http://vyaskn.tripod.com/administration_faq.htm#q14
> so, trace on, restart, traceoff restart?
Yes,,
http://vyaskn.tripod.com/administration_faq.htm#q14
ASKER
i see. very good then, aneesh. it seems i'm out of luck until i can restart the instance. possibly i could profile, but i'm not sure of the specific counters.
there's a particular event taking place that almost always falls victim to a deadlock. i need the flag on, or the profiler running, in order to determine exactly what's causing the problem. but, it's all in prod, so i can't really do it on-demand.
thank you very much for your help, aneesh.
to your knowledge, the whole LOCKS event class in profiler, would that capture everything I'm looking for? (source of the deadlock)
there's a particular event taking place that almost always falls victim to a deadlock. i need the flag on, or the profiler running, in order to determine exactly what's causing the problem. but, it's all in prod, so i can't really do it on-demand.
thank you very much for your help, aneesh.
to your knowledge, the whole LOCKS event class in profiler, would that capture everything I'm looking for? (source of the deadlock)
http://www.sql-server-performance.com/deadlocks.asp