Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

traceon

Posted on 2006-06-21
7
Medium Priority
?
378 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1000 total points
ID: 16953532
dbaSQL,
> no restart necessary, right?
You need to restart it
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

610 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