Deadlock trace(1205,1222) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Deadlock trace(1205,1222)

Author

Topic

buzzi
Starting Member

48 Posts

Posted - 2008-09-29 : 11:42:19

Hi All,
I have turned on the traces 1205,1222 on my production server with a -1
Question:how often i see the trace comments in the error log? because sometimes, i see the messages like below in the error log every few seconds, and some times i dont see any thing for hours?

Starting deadlock search 34887
Target Resource Owner:
ResType:ExchangeId Stype:'AND' SPID:64 BatchID:0 ECID:0 TaskProxy:(0x000000016110E570) Value:0x8018ae80
0:Insert new node: Node:1 ResType:ExchangeId Stype:'AND' SPID:64 BatchID:0 ECID:0 TaskProxy:(0x000000016110E570) Value:0x8018ae80
1:SearchAND Considering new blocker - task: 0000000000FB5828, Worker 0000000159F8E1C0
1:SearchAND Task: 0000000000FB5828 is not blocked on deadlock-detectable resource
1:SearchAND search result: No deadlock
End deadlock search 34887 ... a deadlock was not found.

is there any specific interval, that these traces record something in the error logs

Thanks for the help

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-29 : 11:46:10

You will see when deadlock occurs.you don't have to enable both trace. Just enable DBCC Traceon(1222,3605,-1).

See this:
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-09-29 : 12:14:58

quote:
Originally posted by sodeep
...Just enable DBCC Traceon(1222,3605,-1)...

What additional information does 3605 give you?

Flag 1222 already writes its output to the error log, and SQL 7.0 BOL says that 3605 "Sends trace output to the error log"

CODO ERGO SUM

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-29 : 12:30:29

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by sodeep
...Just enable DBCC Traceon(1222,3605,-1)...

What additional information does 3605 give you?

Flag 1222 already writes its output to the error log, and SQL 7.0 BOL says that 3605 "Sends trace output to the error log"

CODO ERGO SUM

You are correct. don't need for that flag.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources