SQL Server Performance Forum – Threads Archive

Trace flags 1204 and 1205

I have never used trace flags before so I have a few questions: 1. Do I need to restart the sql-server service to have them work? I just wrote "DBCC TRACEON (1204)" in query analyzer… 2. Will the deadlock information appear in the errorlog in EM under SQL Server logs? If nor then how will I be able to read it? 3. Does it steal alot of resources to have these two flags on constantly? Ideally I would like info on all deadlocks… Thanx in advance, you guys rule <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> – Frettmaestro "Real programmers don’t document, if it was hard to write it should be hard to understand"

1) No.
2) If you use SQL Agent, Alerts in EM you have serverals options to alert you.
3) I think there is not alot of resurces.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

Other alternative is use Monitor Performance, Alerts to do allmost the same.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

To add-up Luis’s comments : 1204 – This trace flag returns the type of locks participating in a deadlock and the current command affected. 1205 – This trace flag returns more detailed information about the command being executed at the time of a deadlock. As these flags are necessary when any deadlocks information is required/processed on the server, there is no issue with the resources on SQL server, but ensure SQL log is not bulged heavily. ENsure to turn off the flag once issue is resolved. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Allright, I have had the flags on since yesterday and right now I have one traced deadlock in the errorlog and quite a few of theseresults that I find somewhat odd: Starting deadlock search 8109
…
End deadlock search 8109 … a deadlock was not found. What does this mean? I have about 10 of these loggings in the errorlog since yesterday and there must be a reason why it starts a deadlock-search? Can anybody enlighten me a bit here…? And int the trapped deadlock it says the same for both node 1 and 2 (only different SPID):
Node 1: "SPID: 64 ECID: 0 Statement Type: SELECT Line #: 26"
Node 2: "SPID: 55 ECID: 0 Statement Type: SELECT Line #: 26" But what does select line 26 mean? It also has a reference to the stored procedure that is causing the deadlock, but line 26 is way up in the variable-declaration area…? —
Frettmaestro
"Real programmers don’t document, if it was hard to write it should be hard to understand"

As you’d seen with the log written nothing about deadlock, what is the option used for DEADLOCK_PRIORITY configuration setting. If a deadlock situation continues, it is often useful to use trace flag 1204 to gather more information. Trace flag 1204 prints out the deadlock chains and victim. Can you check execution plan for that SP in query analyzer and if required update the stats, recompile the SP to fine tune the performance. hTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>