January 6, 2009 at 12:52 pm
Hi,
we have sql server 2005 EE x64 on windows 2003 EE x64. Sharepoint application is running on sql server.Share point databases are sitting in sql server. today morning When I saw the error log its full of dead locks.
plz advice what approcah I need to follow now.
Thanks in advance
2009-01-06 09:20:06.28 spid4s Deadlock encountered .... Printing deadlock information
2009-01-06 09:20:06.28 spid4s Wait-for graph
2009-01-06 09:20:06.28 spid4s
2009-01-06 09:20:06.28 spid4s Node:1
2009-01-06 09:20:06.28 spid4s KEY: 20:72057594046644224 (4a007bdc3d8b) CleanCnt:3 Mode:X Flags: 0x0
2009-01-06 09:20:06.28 spid4s Wait List:
2009-01-06 09:20:06.28 spid4s Owner:0x00000003C2891B00 Mode: S Flg:0x2 Ref:1 Life:00000000 SPID:174 ECID:0 XactLockInfo: 0x000000030F3F63A8
2009-01-06 09:20:06.28 spid4s SPID: 174 ECID: 0 Statement Type: SELECT Line #: 136
2009-01-06 09:20:06.28 spid4s Input Buf: RPC Event: Proc [Database Id = 20 Object Id = 2099048]
2009-01-06 09:20:06.28 spid4s Requested By:
2009-01-06 09:20:06.28 spid4s ResType:LockOwner Stype:'OR'Xdes:0x000000040469D110 Mode: S SPID:177 BatchID:0 ECID:0 TaskProxy:(0x00000002B4760598) Value:0xb8935700 Cost:(0/0)
2009-01-06 09:20:06.28 spid4s
2009-01-06 09:20:06.28 spid4s Node:2
2009-01-06 09:20:06.28 spid4s KEY: 20:72057594046644224 (4a007bdc3d8b) CleanCnt:3 Mode:X Flags: 0x0
2009-01-06 09:20:06.28 spid4s Grant List 0:
2009-01-06 09:20:06.28 spid4s Owner:0x000000010137CC80 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:111 ECID:0 XactLockInfo: 0x000000021FA5F258
2009-01-06 09:20:06.28 spid4s SPID: 111 ECID: 0 Statement Type: DELETE Line #: 188
2009-01-06 09:20:06.28 spid4s Input Buf: RPC Event: Proc [Database Id = 20 Object Id = 2099048]
2009-01-06 09:20:06.28 spid4s Requested By:
2009-01-06 09:20:06.28 spid4s ResType:LockOwner Stype:'OR'Xdes:0x000000030F3F6370 Mode: S SPID:174 BatchID:0 ECID:0 TaskProxy:(0x000000030F42C598) Value:0xc2891b00 Cost:(0/0)
2009-01-06 09:20:06.28 spid4s
2009-01-06 09:20:06.28 spid4s Node:3
2009-01-06 09:20:06.28 spid4s KEY: 20:72057594045661184 (de000921701e) CleanCnt:3 Mode:X Flags: 0x0
2009-01-06 09:20:06.28 spid4s Grant List 1:
2009-01-06 09:20:06.28 spid4s Owner:0x00000001552EAA40 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:173 ECID:0 XactLockInfo: 0x000000010E756DB8
2009-01-06 09:20:06.28 spid4s SPID: 173 ECID: 0 Statement Type: DELETE Line #: 188
2009-01-06 09:20:06.28 spid4s Input Buf: RPC Event: Proc [Database Id = 20 Object Id = 2099048]
2009-01-06 09:20:06.28 spid4s Requested By:
2009-01-06 09:20:06.28 spid4s ResType:LockOwner Stype:'OR'Xdes:0x000000021FA5F220 Mode: U SPID:111 BatchID:0 ECID:0 TaskProxy:(0x00000001D84CC598) Value:0xa2ea8400 Cost:(0/3772)
2009-01-06 09:20:06.28 spid4s
2009-01-06 09:20:06.28 spid4s Node:4
2009-01-06 09:20:06.28 spid4s KEY: 20:72057594045661184 (dd00ea26ff90) CleanCnt:3 Mode:X Flags: 0x0
2009-01-06 09:20:06.28 spid4s Wait List:
2009-01-06 09:20:06.28 spid4s Owner:0x00000000982E8440 Mode: S Flg:0x2 Ref:1 Life:00000000 SPID:98 ECID:0 XactLockInfo: 0x000000030AE33598
2009-01-06 09:20:06.28 spid4s SPID: 98 ECID: 0 Statement Type: SELECT Line #: 141
2009-01-06 09:20:06.28 spid4s Input Buf: RPC Event: Proc [Database Id = 20 Object Id = 2099048]
2009-01-06 09:20:06.28 spid4s Requested By:
2009-01-06 09:20:06.28 spid4s ResType:LockOwner Stype:'OR'Xdes:0x000000010E756D80 Mode: U SPID:173 BatchID:0 ECID:0 TaskProxy:(0x000000008E4A0598) Value:0xdb1bab80 Cost:(0/3796)
2009-01-06 09:20:06.28 spid4s
2009-01-06 09:20:06.28 spid4s Node:5
2009-01-06 09:20:06.28 spid4s KEY: 20:72057594045661184 (dd00ea26ff90) CleanCnt:3 Mode:X Flags: 0x0
2009-01-06 09:20:06.28 spid4s Grant List 0:
2009-01-06 09:20:06.28 spid4s Owner:0x00000000BA016440 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:111 ECID:0 XactLockInfo: 0x000000021FA5F258
2009-01-06 09:20:06.28 spid4s Requested By:
2009-01-06 09:20:06.28 spid4s ResType:LockOwner Stype:'OR'Xdes:0x000000030AE33560 Mode: S SPID:98 BatchID:0 ECID:0 TaskProxy:(0x00000001F5978598) Value:0x982e8440 Cost:(0/0)
2009-01-06 09:20:06.28 spid4s
2009-01-06 09:20:06.28 spid4s Victim Resource Owner:
2009-01-06 09:20:06.28 spid4s ResType:LockOwner Stype:'OR'Xdes:0x000000030AE33560 Mode: S SPID:98 BatchID:0 ECID:0 TaskProxy:(0x00000001F5978598) Value:0x982e8440 Cost:(0/0)
2009-01-06 09:20:06.28 spid4s Deadlock encountered .... Printing deadlock information
2009-01-06 09:20:06.28 spid4s Wait-for graph
2009-01-06 09:20:06.28 spid4s
2009-01-06 09:20:06.28 spid4s Node:1
2009-01-06 09:20:06.28 spid4s KEY: 20:72057594046644224 (4a007bdc3d8b) CleanCnt:3 Mode:X Flags: 0x0
2009-01-06 09:20:06.28 spid4s Wait List:
2009-01-06 09:20:06.28 spid4s Owner:0x00000003C2891B00 Mode: S Flg:0x2 Ref:1 Life:00000000 SPID:174 ECID:0 XactLockInfo: 0x000000030F3F63A8
2009-01-06 09:20:06.28 spid4s SPID: 174 ECID: 0 Statement Type: SELECT Line #: 136
2009-01-06 09:20:06.28 spid4s Input Buf: RPC Event: Proc [Database Id = 20 Object Id = 2099048]
2009-01-06 09:20:06.28 spid4s Requested By:
2009-01-06 09:20:06.28 spid4s ResType:LockOwner Stype:'OR'Xdes:0x000000008E1A6700 Mode: S SPID:181 BatchID:0 ECID:0 TaskProxy:(0x000000036706C598) Value:0xcdae3700 Cost:(0/0)
2009-01-06 09:20:06.28 spid4s
2009-01-06 09:20:06.28 spid4s Node:2
2009-01-06 09:20:06.28 spid4s KEY: 20:72057594046644224 (4a007bdc3d8b) CleanCnt:3 Mode:X Flags: 0x0
2009-01-06 09:20:06.28 spid4s Grant List 0:
2009-01-06 09:20:06.28 spid4s Owner:0x000000010137CC80 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:111 ECID:0 XactLockInfo: 0x000000021FA5F258
2009-01-06 09:20:06.28 spid4s SPID: 111 ECID: 0 Statement Type: DELETE Line #: 188
2009-01-06 09:20:06.28 spid4s Input Buf: RPC Event: Proc [Database Id = 20 Object Id = 2099048]
2009-01-06 09:20:06.28 spid4s Requested By:
2009-01-06 09:20:06.28 spid4s ResType:LockOwner Stype:'OR'Xdes:0x000000030F3F6370 Mode: S SPID:174 BatchID:0 ECID:0 TaskProxy:(0x000000030F42C598) Value:0xc2891b00 Cost:(0/0)
2009-01-06 09:20:06.28 spid4s
2009-01-06 09:20:06.28 spid4s Node:3
2009-01-06 09:20:06.28 spid4s KEY: 20:72057594045661184 (de000921701e) CleanCnt:3 Mode:X Flags: 0x0
2009-01-06 09:20:06.28 spid4s Grant List 1:
2009-01-06 09:20:06.28 spid4s Owner:0x00000001552EAA40 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:173 ECID:0 XactLockInfo: 0x000000010E756DB8
2009-01-06 09:20:06.28 spid4s SPID: 173 ECID: 0 Statement Type: DELETE Line #: 188
2009-01-06 09:20:06.28 spid4s Input Buf: RPC Event: Proc [Database Id = 20 Object Id = 2099048]
2009-01-06 09:20:06.28 spid4s Requested By:
2009-01-06 09:20:06.28 spid4s ResType:LockOwner Stype:'OR'Xdes:0x000000021FA5F220 Mode: U SPID:111 BatchID:0 ECID:0 TaskProxy:(0x00000001D84CC598) Value:0xa2ea8400 Cost:(0/3772)
2009-01-06 09:20:06.28 spid4s
2009-01-06 09:20:06.28 spid4s Node:4
2009-01-06 09:20:06.28 spid4s KEY: 20:72057594045661184 (dd00ea26ff90) CleanCnt:3 Mode:X Flags: 0x0
2009-01-06 09:20:06.28 spid4s Wait List:
2009-01-06 09:20:06.28 spid4s Owner:0x00000002EF842C80 Mode: S Flg:0x2 Ref:1 Life:00000000 SPID:168 ECID:0 XactLockInfo: 0x0000000233E983A8
2009-01-06 09:20:06.28 spid4s SPID: 168 ECID: 0 Statement Type: SELECT Line #: 98
2009-01-06 09:20:06.28 spid4s Input Buf: RPC Event: Proc [Database Id = 20 Object Id = 98099390]
2009-01-06 09:20:06.28 spid4s Requested By:
2009-01-06 09:20:06.28 spid4s ResType:LockOwner Stype:'OR'Xdes:0x000000010E756D80 Mode: U SPID:173 BatchID:0 ECID:0 TaskProxy:(0x000000008E4A0598) Value:0xdb1bab80 Cost:(0/3796)
2009-01-06 09:20:06.28 spid4s
2009-01-06 09:20:06.28 spid4s Node:5
2009-01-06 09:20:06.28 spid4s KEY: 20:72057594045661184 (dd00ea26ff90) CleanCnt:3 Mode:X Flags: 0x0
2009-01-06 09:20:06.28 spid4s Grant List 0:
2009-01-06 09:20:06.28 spid4s Owner:0x00000000BA016440 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:111 ECID:0 XactLockInfo: 0x000000021FA5F258
2009-01-06 09:20:06.28 spid4s Requested By:
2009-01-06 09:20:06.28 spid4s ResType:LockOwner Stype:'OR'Xdes:0x0000000233E98370 Mode: S SPID:168 BatchID:0 ECID:0 TaskProxy:(0x00000002166A6598) Value:0xef842c80 Cost:(0/232)
2009-01-06 09:20:06.28 spid4s
2009-01-06 09:20:06.28 spid4s Victim Resource Owner:
2009-01-06 09:20:06.28 spid4s ResType:LockOwner Stype:'OR'Xdes:0x0000000233E98370 Mode: S SPID:168 BatchID:0 ECID:0 TaskProxy:(0x00000002166A6598) Value:0xef842c80 Cost:(0/232)
2009-01-06 09:20:06.28 spid4s Deadlock encountered .... Printing deadlock information
January 6, 2009 at 1:01 pm
Since it's SQL 2005, can you get the deadlock graph using traceflag 1222 instead of 1204 please? The information's a lot more detailed and far easier to read.
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
January 6, 2009 at 1:24 pm
thank you,
we need to add this trace file in sartup parameters in configuration manager or there is another way to do it?
As we need to restart after adding this trace 1222 in startup parameters,I need to inform all the users and restart it and I add the trace. mean while can we get any info that what is causing this dead lock. As share point application is running, we can not modify any Stored procedure is that correct?
and I did not add the trace 1204 in startup parameters. In Sql server 2005 1204 is on by default?
Thanks for your great help
January 6, 2009 at 1:37 pm
madhu.arda (1/6/2009)
we need to add this trace file in sartup parameters in configuration manager or there is another way to do it?
DBCC TRACEON(1222,-1)
and I did not add the trace 1204 in startup parameters. In Sql server 2005 1204 is on by default?
No, it's not.
Possibly a stored proc turns it on, or Shrepoint does. None of the deadlock trace flags are on by default in any version.
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
January 6, 2009 at 3:05 pm
If I do DBCC TRACEON(1222,-1), the trace will be on for ever?
January 6, 2009 at 3:16 pm
madhu.arda (1/6/2009)
If I do DBCC TRACEON(1222,-1), the trace will be on for ever?
Until you turn it off (DBCC TRACEOFF(1222,-1)) or until the SQL service is restarted.
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
January 6, 2009 at 3:38 pm
Thanks gail,
I turned on trace T1222.So far no dead lock is occrured. Could you please give some idea how to diagnose the above dead lock graph initially. I found the procedure which is involved in the dead lock
select object_name(2099048) proc_MSS_ProcessCommitted.
January 14, 2009 at 9:13 am
Ok, let's see....
4 processes involved in the deadlock,
Process 1 - Spid 174, running the select on line 136 of the procedure with ID 2099048 in database 20. It wants a key lock (a single row of an index) within the allocation unit 72057594046644224. Query sys.partitions to get the object id and index id to see what table it is.
Process 2 - Spid 111, running the delete that's on line 188 of the same procedure that Process 1 is using. It has an exclusive lock on the same key as spid 174 has requested, as well as an exclusive lock on a key in the allocation unit 72057594045661184. It has requested an update lock on a different key in the allocation unit 72057594045661184
Process 3 - Spid 173, running thesame delete as spid 188. It has an exclusive lock on a key in the allocation unit 72057594045661184, the one that Spid 111 is waiting for. It has an update lock on a key in the allocation unit
Process 4 - Spid 98. Picked as the deadlock victim. Running the select on line 141 of the same procedure. Requested an exclusive lock on a key in allocation unit 72057594045661184
There's also a key lock without a listed owner.
I'm not sure how to fix this. Since it's a vendor app, you can't make changes to the stored procedures and, with sharepoint, even adding indexes isn't allowed. If you do, MS will not support you any longer (on the sharepoint side)
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
January 14, 2009 at 9:40 am
GilaMonster (1/14/2009)
I'm not sure how to fix this. Since it's a vendor app, you can't make changes to the stored procedures and, with sharepoint, even adding indexes isn't allowed. If you do, MS will not support you any longer (on the sharepoint side)
I had a long running support incident with Microsoft regarding deadlocks in MOSS 2007. They won't fix the issue because the 1205 error returned to SPS is handled and resubmitted. SPS will log in the Windows Event Log that a deadlock occured but it resubmits the transaction back to SQL so the development team does not see it as a problem, while the SQL team at Microsoft agrees it could be fixed with 2 index changes in the database.
Deadlocks are most common during index crawls. If you haven't installed SP1 for MOSS 2007 yet, you might start there since there were deadlock issues that were corrected in SP1, but it doesn't fix them all.
The following is the outcome of our case with Microsoft which was open for over 6 months:
======= START OF CASE SUMMARY =======
Subjective
========
MSSCrawlQueue Deadlock
Objective
=========
Office SharePoint Server 2007 SP1 12.0.6219.1000
Microsoft SQL Server 2005 - 9.00.3152.00 (X64) Enterprise Edition
Windows NT 5.2 (Build 3790: Service Pack 2)
Assessment
=========
Office SharePoint Server 2007 Search does deadlock while crawling. On average it is acceptable to see 1 deadlock for every 200-500 documents crawled or 1 deadlock every 2-3 minutes. It is recommended to run the SQL script in KB (http://support.microsoft.com/kb/943345) to reduce fragmentation of the SharePoint Databases. Follow the recommendation in KB 943345 to determine how often to defragment the SharePoint databases.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply