Solved

How to enable the trace flag for deadlock in sql error log

Posted on 2007-03-29
7
1,543 Views
Last Modified: 2012-06-22
Hi how could I turn in the trace to capture the deadlock information whenever there is a lock, the trace should be able to capture in sql error log
0
Comment
Question by:motioneye
[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 21

Accepted Solution

by:
Kevin3NF earned 500 total points
ID: 18818733
dbcc traceon (1204, 3605)

Or

Set startup parameters:

-T1204
-T3605

and restart the sql server.  This method is necessary if you want the trace flag to stay on after restarts
0
 
LVL 21

Assisted Solution

by:Kevin3NF
Kevin3NF earned 500 total points
ID: 18818742
0
 

Author Comment

by:motioneye
ID: 18818854
Set startup parameters: will the below command work?

sqlservr –d"C:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf" –T1204

sqlservr –d"C:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf" –T3605

0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:motioneye
ID: 18820275
Guys,
I run the dbcc traceon (1204,3605)

But I did not get any log return regarding the deadlock even the server has quite significant number of blocking\locking
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 18822701
Blocking/locking is NOT the same as deadlocking
0
 

Author Comment

by:motioneye
ID: 18829993
so,
is that mean trace flag 1204 will only capture deadlock information?
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 18830401
yes, that is its purpose.  Sp_Lock will show you locking info, and sysprocess as well as sp_who and sp_who2 will show you blocking related data
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 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