[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • 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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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