plagued with a nasty little intermittent deadlock problem, and am in the
middle of trying to correct it. i've received from help from a sql peer who
helped me create a deadlock alert, it worked, and I walked away, intending to
migrate the same logic to my other servers today. It fails on every one of
them.
Thus, I assumed I was missing something obvious, but I've checked/rechecked
everything. I am at a loss. It works on one instance, it fails on every
other server I've tried to migrate it to. (five of them)
sql v2K v 8.0.2039, not using sql mail
i have configured event alerts with error 1205. if a 1205 is found in the
sql error log, the alert responds by invoking a sql agent job -- that job
sends an email alert to my cell phone and email (smtp)
i am nearly certain it is not the alert that is failing. the alert(s) are
never even being invoked. They are event driven - they must see error 1205
in the sql error log before running the agent job which sends me the email
notice.
i've run dbcc traceon (1204,1205,3605)
i've even run EXEC sp_altermessage 1205, with_log, true
i then run two updates in parallel -- to the same instance, just from two
different connections -- one works, one fails with error 1205, BUT the msg
1205 is never written to the sql error log(s).
again, i set this up yesterday on one box, it worked just fine. i have just
now tested the one from yesterday, it works just fine.
i have compared all the 'bad' instances with the good one, i have even
removed and recreated the alerts and the sql agent job. further, i've dbcc
traceoffed everything, and then dbcc traceon'ed it all back
sql agent is running on all machines (at all times, it always does)
but, again, it's not the alert, so it wouldn't be a problem with the agent.
why is Error 1205 not being written to the SQL Error log?:
dbcc tracestatus says this:
1204 1
1205 1
3605 1
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 65) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
-- Lynn
Hi Lynn
I am not sure if this is effected by the service packs you are running, are
they on different builds? Have you checked the trace flag status using DBCC
TRACESTATUS?
John
"Lynn" wrote:
> please let me know what you think, this is very, very important. i am
> plagued with a nasty little intermittent deadlock problem, and am in the
> middle of trying to correct it. i've received from help from a sql peer who
> helped me create a deadlock alert, it worked, and I walked away, intending to
> migrate the same logic to my other servers today. It fails on every one of
> them.
> Thus, I assumed I was missing something obvious, but I've checked/rechecked
> everything. I am at a loss. It works on one instance, it fails on every
> other server I've tried to migrate it to. (five of them)
> sql v2K v 8.0.2039, not using sql mail
> i have configured event alerts with error 1205. if a 1205 is found in the
> sql error log, the alert responds by invoking a sql agent job -- that job
> sends an email alert to my cell phone and email (smtp)
> i am nearly certain it is not the alert that is failing. the alert(s) are
> never even being invoked. They are event driven - they must see error 1205
> in the sql error log before running the agent job which sends me the email
> notice.
> i've run dbcc traceon (1204,1205,3605)
> i've even run EXEC sp_altermessage 1205, with_log, true
> i then run two updates in parallel -- to the same instance, just from two
> different connections -- one works, one fails with error 1205, BUT the msg
> 1205 is never written to the sql error log(s).
> again, i set this up yesterday on one box, it worked just fine. i have just
> now tested the one from yesterday, it works just fine.
> i have compared all the 'bad' instances with the good one, i have even
> removed and recreated the alerts and the sql agent job. further, i've dbcc
> traceoffed everything, and then dbcc traceon'ed it all back
> sql agent is running on all machines (at all times, it always does)
> but, again, it's not the alert, so it wouldn't be a problem with the agent.
> why is Error 1205 not being written to the SQL Error log?:
> dbcc tracestatus says this:
> 1204 1
> 1205 1
> 3605 1
>
> Server: Msg 1205, Level 13, State 50, Line 1
> Transaction (Process ID 65) was deadlocked on lock resources with another
> process and has been chosen as the deadlock victim. Rerun the transaction.
> -- Lynn
|||Yes, John. As I said, DBCC TRACESTATUS gives me this:
> 1204 1
> 1205 1
> 3605 1
And, they're all on exactly the same build -
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
This is becoming terribly urgent. Any thoughts at all?
--Lynn
"John Bell" wrote:
[vbcol=seagreen]
> Hi Lynn
> I am not sure if this is effected by the service packs you are running, are
> they on different builds? Have you checked the trace flag status using DBCC
> TRACESTATUS?
> John
>
> "Lynn" wrote:
|||Hi
I would usually only use trace flags 1204 and 3605. Have you tried putting
them in the startup command and stop/start SQL Server?
John
"Lynn" wrote:
[vbcol=seagreen]
> Yes, John. As I said, DBCC TRACESTATUS gives me this:
>
> And, they're all on exactly the same build -
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
> This is becoming terribly urgent. Any thoughts at all?
> --Lynn
>
> "John Bell" wrote:
|||Yes, but the resources won't be booted until saturday evening. I was also
thinking I may want to put 1204 in there, instead of 1205, as I believe it
writes a bit less to the log...but, i just now had a deadlock and i only see
1205 in there (the box that is working). Under what conditions is 1204
written?
Also, regardless, do you have any thoughts at all as to why 1205 isn't being
reported? Here's a bit of the log, a deadlock is obviously occurring, where
is 1205?
2006-11-01 08:52:03.86 spid1
Deadlock encountered ... Printing deadlock information
2006-11-01 08:52:03.86 spid1
2006-11-01 08:52:03.86 spid1 Wait-for graph
2006-11-01 08:52:03.86 spid1
2006-11-01 08:52:03.86 spid1 Node:1
2006-11-01 08:52:03.86 spid1 RID: 7:3:36:1 CleanCnt:2
Mode: X Flags: 0x2
2006-11-01 08:52:03.86 spid1 Grant List 0::
2006-11-01 08:52:03.86 spid1 Owner:0x7dbd0c20 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:72 ECID:0
2006-11-01 08:52:03.86 spid1 SPID: 72 ECID: 0 Statement Type: UPDATE
Line #: 1
2006-11-01 08:52:03.86 spid1 Input Buf: Language Event:
BEGIN TRAN
UPDATE t2 SET i = 1 WHERE i = 1
WAITFOR DELAY '00:00:10'
UPDATE t1 SET i = 9 WHERE i = 9
COMMIT
2006-11-01 08:52:03.86 spid1 Requested By:
2006-11-01 08:52:03.86 spid1 ResType:LockOwner Stype:'OR' Mode: U
SPID:66 ECID:0 Ec
0x264EB548) Value:0x80269060 Cost0/54)2006-11-01 08:52:03.86 spid1
2006-11-01 08:52:03.86 spid1 Node:2
2006-11-01 08:52:03.86 spid1 RID: 7:3:34:0 CleanCnt:2
Mode: X Flags: 0x2
2006-11-01 08:52:03.86 spid1 Grant List 0::
2006-11-01 08:52:03.86 spid1 Owner:0x8027ce80 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:66 ECID:0
2006-11-01 08:52:03.86 spid1 SPID: 66 ECID: 0 Statement Type: UPDATE
Line #: 1
2006-11-01 08:52:03.86 spid1 Input Buf: Language Event: --copy in
second window:
BEGIN TRAN
UPDATE t1 SET i = 9 WHERE i = 9
WAITFOR DELAY '00:00:10'
UPDATE t2 SET i = 1 WHERE i = 1
COMMIT
2006-11-01 08:52:03.86 spid1 Requested By:
2006-11-01 08:52:03.86 spid1 ResType:LockOwner Stype:'OR' Mode: U
SPID:72 ECID:0 Ec
0x9D473560) Value:0x8027c9a0 Cost0/54)2006-11-01 08:52:03.86 spid1 Victim Resource Owner:
2006-11-01 08:52:03.86 spid1 ResType:LockOwner Stype:'OR' Mode: U
SPID:72 ECID:0 Ec
0x9D473560) Value:0x8027c9a0 Cost0/54)2006-11-01 08:52:03.86 spid1
2006-11-01 08:52:03.86 spid1 End deadlock search 5483 ... a deadlock was
found.
2006-11-01 08:52:03.86 spid1 --
Lynn
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> I would usually only use trace flags 1204 and 3605. Have you tried putting
> them in the startup command and stop/start SQL Server?
> John
> "Lynn" wrote:
|||Hi Lynn
Error 1204 gets displayed when you run out of lock resources, it is not
really a deadlock. Trace Flag 1204 will display information on what locks are
participating in a deadlock. Trace Flag 1205 will displays a stack trace when
deadlocks are detected. Trace flag 3605 will direct this to the error log. It
sounds like Trace Flag 3605 is not working for some reason.
John
"Lynn" wrote:
[vbcol=seagreen]
> Yes, but the resources won't be booted until saturday evening. I was also
> thinking I may want to put 1204 in there, instead of 1205, as I believe it
> writes a bit less to the log...but, i just now had a deadlock and i only see
> 1205 in there (the box that is working). Under what conditions is 1204
> written?
> Also, regardless, do you have any thoughts at all as to why 1205 isn't being
> reported? Here's a bit of the log, a deadlock is obviously occurring, where
> is 1205?
> 2006-11-01 08:52:03.86 spid1
> Deadlock encountered ... Printing deadlock information
> 2006-11-01 08:52:03.86 spid1
> 2006-11-01 08:52:03.86 spid1 Wait-for graph
> 2006-11-01 08:52:03.86 spid1
> 2006-11-01 08:52:03.86 spid1 Node:1
> 2006-11-01 08:52:03.86 spid1 RID: 7:3:36:1 CleanCnt:2
> Mode: X Flags: 0x2
> 2006-11-01 08:52:03.86 spid1 Grant List 0::
> 2006-11-01 08:52:03.86 spid1 Owner:0x7dbd0c20 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:72 ECID:0
> 2006-11-01 08:52:03.86 spid1 SPID: 72 ECID: 0 Statement Type: UPDATE
> Line #: 1
> 2006-11-01 08:52:03.86 spid1 Input Buf: Language Event:
> BEGIN TRAN
> UPDATE t2 SET i = 1 WHERE i = 1
> WAITFOR DELAY '00:00:10'
> UPDATE t1 SET i = 9 WHERE i = 9
> COMMIT
> 2006-11-01 08:52:03.86 spid1 Requested By:
> 2006-11-01 08:52:03.86 spid1 ResType:LockOwner Stype:'OR' Mode: U
> SPID:66 ECID:0 Ec
0x264EB548) Value:0x80269060 Cost0/54)> 2006-11-01 08:52:03.86 spid1
> 2006-11-01 08:52:03.86 spid1 Node:2
> 2006-11-01 08:52:03.86 spid1 RID: 7:3:34:0 CleanCnt:2
> Mode: X Flags: 0x2
> 2006-11-01 08:52:03.86 spid1 Grant List 0::
> 2006-11-01 08:52:03.86 spid1 Owner:0x8027ce80 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:66 ECID:0
> 2006-11-01 08:52:03.86 spid1 SPID: 66 ECID: 0 Statement Type: UPDATE
> Line #: 1
> 2006-11-01 08:52:03.86 spid1 Input Buf: Language Event: --copy in
> second window:
> BEGIN TRAN
> UPDATE t1 SET i = 9 WHERE i = 9
> WAITFOR DELAY '00:00:10'
> UPDATE t2 SET i = 1 WHERE i = 1
> COMMIT
> 2006-11-01 08:52:03.86 spid1 Requested By:
> 2006-11-01 08:52:03.86 spid1 ResType:LockOwner Stype:'OR' Mode: U
> SPID:72 ECID:0 Ec
0x9D473560) Value:0x8027c9a0 Cost0/54)> 2006-11-01 08:52:03.86 spid1 Victim Resource Owner:
> 2006-11-01 08:52:03.86 spid1 ResType:LockOwner Stype:'OR' Mode: U
> SPID:72 ECID:0 Ec
0x9D473560) Value:0x8027c9a0 Cost0/54)> 2006-11-01 08:52:03.86 spid1
> 2006-11-01 08:52:03.86 spid1 End deadlock search 5483 ... a deadlock was
> found.
> 2006-11-01 08:52:03.86 spid1 --
> --
> Lynn
>
> "John Bell" wrote:
|||Agreed. Precisely. Every piece of this thing is working -- except 3605. I
don't know what to do. I guess I'm calling msft.
-- Lynn
"John Bell" wrote:
[vbcol=seagreen]
> Hi Lynn
> Error 1204 gets displayed when you run out of lock resources, it is not
> really a deadlock. Trace Flag 1204 will display information on what locks are
> participating in a deadlock. Trace Flag 1205 will displays a stack trace when
> deadlocks are detected. Trace flag 3605 will direct this to the error log. It
> sounds like Trace Flag 3605 is not working for some reason.
> John
> "Lynn" wrote:
|||Hi Lynn
If the reboot doesn't work you may want to. As you are on Enterprise Edition
I would suggest that you think about moving to 2040 or higher even if you
aren't using AWE.
Have you tried using sp_blocker_pss80 from
http://support.microsoft.com/default.aspx/kb/271509/ ?
John
"Lynn" wrote:
[vbcol=seagreen]
> Agreed. Precisely. Every piece of this thing is working -- except 3605. I
> don't know what to do. I guess I'm calling msft.
> -- Lynn
>
> "John Bell" wrote:
|||No, John, I've never even seen that (sp_blocker...). What can you tell me
about it?
-- Lynn
"John Bell" wrote:
[vbcol=seagreen]
> Hi Lynn
> If the reboot doesn't work you may want to. As you are on Enterprise Edition
> I would suggest that you think about moving to 2040 or higher even if you
> aren't using AWE.
> Have you tried using sp_blocker_pss80 from
> http://support.microsoft.com/default.aspx/kb/271509/ ?
> John
> "Lynn" wrote:
|||Hi Lynn
You create the procedure as defined in the article and then run it in a
loop. I usually run it more often than the value specified in the sample.
When clocking occurs it will give you the spids and input buffers of the
processes that are blocking and blocked.
John
"Lynn" wrote:
[vbcol=seagreen]
> No, John, I've never even seen that (sp_blocker...). What can you tell me
> about it?
> -- Lynn
>
> "John Bell" wrote:
No comments:
Post a Comment