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

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
motioneyeAsked:
Who is Participating?
 
Kevin HillSr. SQL Server DBACommented:
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
 
Kevin HillSr. SQL Server DBACommented:
0
 
motioneyeAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
motioneyeAuthor Commented:
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
 
Kevin HillSr. SQL Server DBACommented:
Blocking/locking is NOT the same as deadlocking
0
 
motioneyeAuthor Commented:
so,
is that mean trace flag 1204 will only capture deadlock information?
0
 
Kevin HillSr. SQL Server DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.