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

Posted on 2007-03-29
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
Question by:motioneye
  • 4
  • 3
LVL 21

Accepted Solution

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


Set startup parameters:


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

Assisted Solution

Kevin3NF earned 500 total points
ID: 18818742

Author Comment

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.


Author Comment

ID: 18820275
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
LVL 21

Expert Comment

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

Author Comment

ID: 18829993
is that mean trace flag 1204 will only capture deadlock information?
LVL 21

Expert Comment

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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now