Solved

traceon

Posted on 2006-06-21
7
367 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:dbaSQL
  • 4
  • 3
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16953253
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16953321
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
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 250 total points
ID: 16953532
dbaSQL,
> no restart necessary, right?
You need to restart it
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16953545
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16953550
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
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16953600
dbaSQL,
> so, trace on, restart, traceoff restart?

Yes,,
http://vyaskn.tripod.com/administration_faq.htm#q14

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 16953718
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)
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now