Trace Flags Deadlocks - Am i missing something?

Question

Trace Flags Deadlocks - Am i missing something?

curious_sqldba

SSC-Dedicated

Points: 36502
More actions
March 14, 2011 at 5:11 pm

#248192

First of all, i have done enough search on this forum and did find some good posts regarding dead locks.However i couldn't find a post(or may be i missed 😛 it) which talks about restarting sql server or not. Mentioned below are my questions please clarify:
i) trace 1222, will enable XML deadlock graph in sql server. Do i need to restart the server?
ii) once i setup my deadlock through query how long is it valid?
iii) do i ALWAYS have to enable 3605 along 1222? Seems like i do from this article "http://www.sqlservercentral.com/articles/deadlock/71374/".
Please clarfiy!!Thanks

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

sqldba_icon (3/14/2011)
i) trace 1222, will enable XML deadlock graph in sql server. Do i need to restart the server?

Depends how you enable it. If you use TRACEON, no, if you use the startup parameters, yes

ii) once i setup my deadlock through query how long is it valid?

Deadlock in a query or deadlock trace flag? I don't understand what you're asking here.

iii) do i ALWAYS have to enable 3605 along 1222? Seems like i do from this article "http://www.sqlservercentral.com/articles/deadlock/71374/".

You don't have to enable 3605 at all. All 3605 does is redirect some outputs (like from DBCC Page) to the error log. It has no effect on deadlock graph and is not needed here at all

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

curious_sqldba SSC-Dedicated Points: 36502 More actions · Answer 2

Thanks. I am sorry i couldn't put my question right. Mentioned below is the query i am using to enable deadlocks

dbcc traceon(1222,-1)

My question is will this trace be enabled always or only until next restart?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Only until the next restart.

If you want it permanent, it needs to be added as a traceflag

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 4

Gail is right. Follow her advice.

--

Best Regards,

SQLBuddy

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

sqlbuddy123 (3/15/2011)
Gail is right. Follow her advice.

I think you may have started a new SSC mantra...

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA