[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

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?
0
dbaSQL
Asked:
dbaSQL
  • 4
  • 3
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
dbaSQLAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
dbaSQL,
> no restart necessary, right?
You need to restart it
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
dbaSQLAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
dbaSQL,
> so, trace on, restart, traceoff restart?

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

0
 
dbaSQLAuthor Commented:
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

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now