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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud


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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
syadmin MSSQL 2 57
MS SQL - Conversion failed when converting the varchar value 3 22
Permissions on Database 11 36
Counting connections to SQL Server through C# 3 24
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
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…

914 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

17 Experts available now in Live!

Get 1:1 Help Now