January 22, 2009 at 10:16 am
Hi,
we are using sql server 2005 EE x64. Sharepoint databases are stored in this instance. We got a dead lock this morning.
what should I do to aviod this deadlocks?
2009-01-22 06:29:28.24 spid20s deadlock-list
2009-01-22 06:29:28.24 spid20s deadlock victim=process538cc58
2009-01-22 06:29:28.24 spid20s process-list
2009-01-22 06:29:28.24 spid20s process id=processff1048 taskpriority=0 logused=3680 waitresource=KEY: 20:72057594045661184 (9500765ecfe8) waittime=3125 ownerId=49235532 transactionname=user_transaction lasttranstarted=2009-01-22T06:29:25.090 XDES=0xc84bb4a0 lockMode=U schedulerid=4 kpid=752 status=suspended spid=173 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-01-22T06:29:25.090 lastbatchcompleted=2009-01-22T06:29:25.090 clientapp=Windows SharePoint Services hostname=ABC hostpid=5424 loginname=ABC\spsearch isolationlevel=read committed (2) xactid=49235532 currentdb=20 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128056
2009-01-22 06:29:28.24 spid20s executionStack
2009-01-22 06:29:28.24 spid20s frame procname=SharedServices1_Search_DB.dbo.proc_MSS_ProcessDelete line=188 stmtstart=15130 stmtend=15264 sqlhandle=0x030014002fe32b7fc0ae9500fe9a00000100000000000000
2009-01-22 06:29:28.24 spid20s DELETE FROM MSSCrawlQueue WHERE DocID=@DocID AND BatchID = 0
2009-01-22 06:29:28.24 spid20s frame procname=SharedServices1_Search_DB.dbo.proc_MSS_ProcessCommitted line=285 stmtstart=21062 stmtend=22716 sqlhandle=0x0300140068072000caae9500fe9a00000100000000000000
2009-01-22 06:29:28.24 spid20s EXEC dbo.proc_MSS_ProcessDelete
2009-01-22 06:29:28.24 spid20s @ProjectID,
2009-01-22 06:29:28.24 spid20s @HisContentSourceID,
2009-01-22 06:29:28.24 spid20s @StartAddressID,
2009-01-22 06:29:28.24 spid20s @DocID,
2009-01-22 06:29:28.24 spid20s @CrawlID,
2009-01-22 06:29:28.24 spid20s @HisCrawlID,
2009-01-22 06:29:28.24 spid20s @HisCommitCrawlID,
2009-01-22 06:29:28.24 spid20s @Scope,
2009-01-22 06:29:28.24 spid20s @HisTransactionFlags,
2009-01-22 06:29:28.24 spid20s @UseChangeLog,
2009-01-22 06:29:28.24 spid20s @ChangeLogCookie,
2009-01-22 06:29:28.24 spid20s @ChangeLogBatchID,
2009-01-22 06:29:28.24 spid20s @DeleteReason,
2009-01-22 06:29:28.24 spid20s @TransactionType,
2009-01-22 06:29:28.24 spid20s @HostDepth,
2009-01-22 06:29:28.24 spid20s @EnumerationDepth,
2009-01-22 06:29:28.24 spid20s @HisParentDocID,
2009-01-22 06:29:28.24 spid20s @EndPathFlag,
2009-01-22 06:29:28.24 spid20s @HostID,
2009-01-22 06:29:28.24 spid20s @LCID,
2009-01-22 06:29:28.24 spid20s @ErrorID,
2009-01-22 06:29:28.24 spid20s @ErrorLevel,
2009-01-22 06:29:28.24 spid20s @AccessURL,
2009-01-22 06:29:28.24 spid20s @AccessHash,
2009-01-22 06:29:28.24 spid20s @CompactURL,
2009-01-22 06:29:28.24 spid20s @CompactHash,
2009-01-22 06:29:28.24 spid20s @DisplayURL,
2009-01-22 06:29:28.24 spid20s @DisplayHash,
2009-01-22 06:29:28.24 spid20s @MaxDocId
2009-01-22 06:29:28.24 spid20s inputbuf
2009-01-22 06:29:28.24 spid20s Proc [Database Id = 20 Object Id = 2099048]
2009-01-22 06:29:28.24 spid20s process id=process538cc58 taskpriority=0 logused=1628 waitresource=KEY: 20:72057594045923328 (310055b059c1) waittime=3140 ownerId=49235531 transactionname=user_transaction lasttranstarted=2009-01-22T06:29:25.090 XDES=0xa4956d80 lockMode=S schedulerid=6 kpid=4972 status=suspended spid=63 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-01-22T06:29:25.090 lastbatchcompleted=2009-01-22T06:29:25.090 clientapp=Windows SharePoint Services hostname=ABC hostpid=5424 loginname=ABC\spsearch isolationlevel=read committed (2) xactid=49235531 currentdb=20 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128056
2009-01-22 06:29:28.24 spid20s executionStack
2009-01-22 06:29:28.24 spid20s frame procname=adhoc line=1 stmtstart=60 sqlhandle=0x02000000dbd76e024f8a3ca3a9fd3ef41514c0ad35cbced1
2009-01-22 06:29:28.24 spid20s UPDATE MSSCrawlQueue SET BatchID = @BatchID, Priority = 2
2009-01-22 06:29:28.24 spid20s FROM MSSCrawlQueue A
2009-01-22 06:29:28.24 spid20s INNER JOIN (
2009-01-22 06:29:28.24 spid20s SELECT TOP 20475 CrawlID, FolderHighPriority, ItemHighPriority, SeqID
2009-01-22 06:29:28.24 spid20s FROM MSSCrawlQueue
2009-01-22 06:29:28.24 spid20s WHERE CrawlID = @CrawlID
2009-01-22 06:29:28.24 spid20s AND BatchID = 0
2009-01-22 06:29:28.24 spid20s ORDER BY SEQID ) AS B
2009-01-22 06:29:28.24 spid20s ON A.CrawlID = B.CrawlID AND A.FolderHighPriority = B.FolderHighPriority AND A.ItemHighPriority = B.ItemHighPriority AND A.SeqID = B.SeqID
2009-01-22 06:29:28.24 spid20s frame procname=mssqlsystemresource.sys.sp_executesql line=1 sqlhandle=0x0400ff7fbe80662601000000000000000000000000000000
2009-01-22 06:29:28.24 spid20s sp_executesql
2009-01-22 06:29:28.24 spid20s frame procname=SharedServices1_Search_DB.dbo.proc_MSS_GetNextCrawlBatch line=157 stmtstart=12754 stmtend=12876 sqlhandle=0x03001400bee0d8050baf9500fe9a00000100000000000000
2009-01-22 06:29:28.24 spid20s EXEC SP_EXECUTESQL @Stmt, @PARAM, @BatchID, @CrawlID
2009-01-22 06:29:28.24 spid20s inputbuf
2009-01-22 06:29:28.24 spid20s Proc [Database Id = 20 Object Id = 98099390]
2009-01-22 06:29:28.24 spid20s resource-list
2009-01-22 06:29:28.24 spid20s keylock hobtid=72057594045661184 dbid=20 objectname=SharedServices1_Search_DB.dbo.MSSCrawlQueue indexname=IX_MSSCrawlQueue_Cluster id=lock919dd880 mode=X associatedObjectId=72057594045661184
2009-01-22 06:29:28.24 spid20s owner-list
2009-01-22 06:29:28.24 spid20s owner id=process538cc58 mode=X
2009-01-22 06:29:28.24 spid20s waiter-list
2009-01-22 06:29:28.24 spid20s waiter id=processff1048 mode=U requestType=wait
2009-01-22 06:29:28.24 spid20s keylock hobtid=72057594045923328 dbid=20 objectname=SharedServices1_Search_DB.dbo.MSSCrawlQueue indexname=IX_MSSCrawlQueue_SeqID id=lockb7dfa380 mode=X associatedObjectId=72057594045923328
2009-01-22 06:29:28.24 spid20s owner-list
2009-01-22 06:29:28.24 spid20s owner id=processff1048 mode=X
2009-01-22 06:29:28.24 spid20s waiter-list
2009-01-22 06:29:28.24 spid20s waiter id=process538cc58 mode=S requestType=wait
Thank You
January 22, 2009 at 10:29 am
Unfortunately, with sharepoint, there's not much you can do. You can't change the code and if you add any indexes, MS will no longer support you. From what I understand, the search is 'expected' to deadlock and will resume correctly when that happens.
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 22, 2009 at 10:45 am
Thanks Gail,
So there is no way we can reduce the frequeny of getting dead locks in this scenario?any thing we can advice to the sharepoint admin to avoid this deadlocks?
January 22, 2009 at 5:30 pm
Hi Gail,
Is these dead locks are anyway related to having different collation settings.
we have sharepont databases,which will be installated by default with collation settings of Latin1_General_CI_AS_KS_WS. But when we installed sql server we choose the default collation settings SQL_Latin1_General_CP1_CI_AS.
System databases: collation: SQL_Latin1_General_CP1_CI_AS
User databases:collation: Latin1_General_CI_AS_KS_WS
Thanks again
January 23, 2009 at 12:28 am
madhu.arda (1/22/2009)
Is these dead locks are anyway related to having different collation settings.
Unlikely. Different collation would give you a collation mismatch error if there was a problem.
All I know about this is that someone called MS support, spoke to the sharepoint people about these deadlocks and was told that it's 'normal' and 'expected' and they weren't to worry. The SQL people at MS support proposed an index, the sharepoint people noted that adding an index was not permitted by the sharepoint licence and, as such, doing so would mean that MS would never support that installation again.
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 23, 2009 at 3:13 pm
In general when we install sql server 2005, which is going to store sharepoint databases, do we need to choose the collation settings as collation: Latin1_General_CI_AS_KS_WS instead of default collation settings SQL_Latin1_General_CP1_CI_AS?
Because, I recently came to know that when we install sharepoint, it creates the databases in sql server with the collations settings Latin1_General_CI_AS_KS_WS.
Unfortunately, when we installed the sql server, we don not know that? what are things I can do now inorder to aviod these collation mismatch in future?
Thanks for your help
April 3, 2009 at 8:06 am
Did you get anywhere with this ..........we have same issue but not just one deadlock probrably 100 of them...at a time.
April 3, 2009 at 12:57 pm
Open a case with MS.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply