SQL Server Error log deadlock occurrence
There are frequent deadlocks occurring on the server mainly in relation to SharePoint databases.
Stored procedures mainly involved in deadlocking :
·
proc_MSS_ProcessCommitted
·
proc_MSS_UpdateHostStats
· proc_MSS_ProcessDelete
·
proc_MSS_GetNextCrawlBatch
any one can resolve this issue?
This below is the Error log report
2010-12-06 16:44:12.18 spid15s
deadlock-list
2010-12-06 16:44:12.18 spid15s
deadlock victim=process32ab5b8
2010-12-06 16:44:12.18 spid15s
process-list
2010-12-06 16:44:12.18 spid15s
process id=process2f69d38 taskpriority=0 logused=4500
waitresource=KEY : 94:72057594042777600 (52004123c266) waittime=734 ownerId=2117346
transactionname=user_transaction lasttranstarted=2010-12-06T16:44:11.370 XDES=0x3de6a250
lockMode=U schedulerid=10 kpid=7124 status=suspended
spid=500 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2010-12-06T16:44:11.370 lastbatchcompleted=2010-12-06T16:44:11.370
clientapp=Windows SharePoint Services
hostname=servername
hostpid=3300 loginname=name isolationlevel=read committed (2) xactid=2117346
currentdb=WSS_Search_DB_21052010 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128056
2010-12-06 16:44:12.18 spid15s
executionStack
2010-12-06 16:44:12.18 spid15s
frame procname=WSS_Search_DB_21052010.dbo.proc_MSS_ProcessDelete
line=162 stmtstart=13936 stmtend=14066 sqlhandle=0x03005e0084764f7ce7d43c017d9d00000100000000000000
2010-12-06 16:44:12.18 spid15s
DELETE FROM MSSCrawlQueue WHERE DocID=@DocID AND BatchID = 0
2010-12-06 16:44:12.18 spid15s
frame procname=WSS_Search_DB_21052010.dbo.proc_MSS_ProcessCommitted
line=249 stmtstart=20918 stmtend=22572 sqlhandle=0x03005e00bd9a437d4fd53c017d9d00000100000000000000
2010-12-06 16:44:12.18 spid15s
EXEC dbo.proc_MSS_ProcessDelete
2010-12-06 16:44:12.18 spid15s
@ProjectID,
2010-12-06 16:44:12.18 spid15s
@HisContentSourceID,
2010-12-06 16:44:12.18 spid15s
@StartAddressID,
2010-12-06 16:44:12.18 spid15s
@DocID,
2010-12-06 16:44:12.18 spid15s
@CrawlID,
2010-12-06 16:44:12.18 spid15s
@HisCrawlID,
2010-12-06 16:44:12.18 spid15s
@HisCommitCrawlID,
2010-12-06 16:44:12.18 spid15s
@Scope,
2010-12-06 16:44:12.18 spid15s
@HisTransactionFlags,
2010-12-06 16:44:12.18 spid15s
@UseChangeLog,
2010-12-06 16:44:12.18 spid15s
@ChangeLogCookie,
2010-12-06 16:44:12.18 spid15s
@ChangeLogBatchID,
2010-12-06 16:44:12.18 spid15s
@DeleteReason,
2010-12-06 16:44:12.18 spid15s
@TransactionType,
2010-12-06 16:44:12.18 spid15s
@HostDepth,
2010-12-06 16:44:12.18 spid15s
@EnumerationDepth,
2010-12-06 16:44:12.18 spid15s
@HisParentDocID,
2010-12-06 16:44:12.18 spid15s
@EndPathFlag,
2010-12-06 16:44:12.18 spid15s
@HostID,
2010-12-06 16:44:12.18 spid15s
@LCID,
2010-12-06 16:44:12.18 spid15s
@ErrorID,
2010-12-06 16:44:12.18 spid15s
@ErrorLevel,
2010-12-06 16:44:12.18 spid15s
@AccessURL,
2010-12-06 16:44:12.18 spid15s
@AccessHash,
2010-12-06 16:44:12.18 spid15s
@CompactURL,
2010-12-06 16:44:12.18 spid15s
@CompactHash,
2010-12-06 16:44:12.18 spid15s
@DisplayURL,
2010-12-06 16:44:12.18 spid15s
@DisplayHash,
2010-12-06 16:44:12.18 spid15s
@MaxDocId
2010-12-06 16:44:12.18 spid15s
inputbuf
2010-12-06 16:44:12.18 spid15s
Proc [Database Id = 94 Object Id = 2101582525]
2010-12-06 16:44:12.18 spid15s
process id=process32ab5b8 taskpriority=0 logused=820
waitresource=KEY : 94:72057594042580992 (020068e8b274) waittime=781 ownerId=2117356
transactionname=user_transaction lasttranstarted=2010-12-06T16:44:11.377 XDES=0x3d11c250
lockMode=X schedulerid=13 kpid=4852 status=suspended
spid=498 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2010-12-06T16:44:11.377 lastbatchcompleted=2010-12-06T16:44:11.377
clientapp=Windows SharePoint Services
hostname=ServerNamehostpid=3300
loginname=name isolationlevel=read committed (2) xactid=2117356
currentdb=WSS_Search_DB_21052010 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128056
2010-12-06 16:44:12.18 spid15s
executionStack
2010-12-06 16:44:12.18 spid15s
frame procname=WSS_Search_DB_21052010.dbo.proc_MSS_UpdateHostStats
line=35 stmtstart=3098 sqlhandle=0x03005e004b525b7be1d43c017d9d00000100000000000000
2010-12-06 16:44:12.18 spid15s
UPDATE MSSCrawlHostList SET
2010-12-06 16:44:12.18 spid15s
SuccessCount = CASE WHEN SuccessCount + @SuccessCount > 0 THEN SuccessCount + @SuccessCount ELSE 0 END,
2010-12-06 16:44:12.18 spid15s
ErrorCount = CASE WHEN ErrorCount + @ErrorCount > 0 THEN ErrorCount + @ErrorCount ELSE 0 END,
2010-12-06 16:44:12.18 spid15s
WarningCount = CASE WHEN WarningCount + @WarningCount > 0 THEN WarningCount + @WarningCount ELSE 0 END
2010-12-06 16:44:12.18 spid15s
WHERE HostID = @HostID
2010-12-06 16:44:12.18 spid15s
frame procname=WSS_Search_DB_21052010.dbo.proc_MSS_ProcessCommitted
line=132 stmtstart=12972 stmtend=13300 sqlhandle=0x03005e00bd9a437d4fd53c017d9d00000100000000000000
2010-12-06 16:44:12.18 spid15s
EXEC dbo.proc_MSS_UpdateHostStats @HostID, @TransactionType, @MarkDelete, @ErrorID, @ErrorLevel, @HisCommitCrawlID, @HisErrorLevel, @TrackIDDel, @DelErrorLevel
2010-12-06 16:44:12.18 spid15s
inputbuf
2010-12-06 16:44:12.18 spid15s
Proc [Database Id = 94 Object Id = 2101582525]
2010-12-06 16:44:12.18 spid15s
resource-list
2010-12-06 16:44:12.18 spid15s
keylock hobtid=72057594042777600 dbid=94
objectname=WSS_Search_DB_21052010.dbo.MSSCrawlQueue
indexname=IX_MSSCrawlQueue_Cluster id=lock1edb5640 mode=X associatedObjectId=72057594042777600
2010-12-06 16:44:12.18 spid15s
owner-list
2010-12-06 16:44:12.18 spid15s
owner id=process32ab5b8 mode=X
2010-12-06 16:44:12.18 spid15s
waiter-list
2010-12-06 16:44:12.18 spid15s
waiter id=process2f69d38 mode=U requestType=wait
2010-12-06 16:44:12.18 spid15s
keylock hobtid=72057594042580992 dbid=94
objectname=WSS_Search_DB_21052010.dbo.MSSCrawlHostList
indexname=PK_MSSCrawlHostList id=lock31b6f180 mode=X associatedObjectId=72057594042580992
2010-12-06 16:44:12.18 spid15s
owner-list
2010-12-06 16:44:12.18 spid15s
owner id=process2f69d38 mode=X
2010-12-06 16:44:12.18 spid15s
waiter-list
2010-12-06 16:44:12.18 spid15s
waiter id=process32ab5b8 mode=X requestType=wait
2010-12-06 16:44:17.23 spid14s
deadlock-list
January 12th, 2011 6:41am
Based on the stored procedures provided, it seems that a crawl was running during the time of the deadlock. In order to isolate crawl being the culprit, disable crawl for a day and see if the issue reoccurs.
Thanks,
Dip
Free Windows Admin Tool Kit Click here and download it now
January 14th, 2011 9:51pm
Do you have any out of the box Content Deployment is running?
January 15th, 2011 10:54pm
Do you have any out of the box Content Deployment is running?
Free Windows Admin Tool Kit Click here and download it now
January 15th, 2011 10:54pm
Do you have any out of the box Content Deployment is running?
January 15th, 2011 10:54pm
This topic is archived. No further replies will be accepted.
Other recent topics
