Solved

traceon

Posted on 2006-06-21
7
375 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 250 total points
ID: 16953532
dbaSQL,
> no restart necessary, right?
You need to restart it
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

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!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

738 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