Solved

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

Posted on 2007-03-29
7
1,529 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
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

831 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