Deadlock - One node is in the Resource database?
-
Anyone ever experience this? I'm troubleshooting several issues with a client and have come across Deadlocks that involve an object in the Resource Database and one in the user's production database.
I'm leaning toward this beng related to the fact that the client's software uses several custom functions that were placed in the master datbase.
Looking for any similar experiences. 🙂
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Custom functions in master would show master as the source.
Post the deadlock graph? I'd guess there's some built-in function or procedure involved (those are stored in the resource DB)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I'll need to get permission from the client to turn on the trace, but in the meantime here is the limited information from the SQL Error Log.
** Each of these line items are a single line. WYSIWYG!
[highlight]I should also mention this server participates in Merge Replication.[/highlight]
I was able to id one node by the objects identified in the image above, but can't seem to get the object info for Node 2 since it's in the "hidden" Resource DB.
Node 1 refers to a procedure in the "production" database. The procedure performs a few simple selects, an update and a call to another proc. which performs a single insert. The only functions involved on this node are getdate, isnull and convert. No triggers (other than the replication triggers) on any table involved. This proc is very straight forward, no complex queries, cursors or logic holes (loops etc.). All tables involved are small (< 10 k rows)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Err.. where?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (10/10/2013)
Err.. where?Ok, what are you asking? Where what? 🙂
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Jason Selburg (10/10/2013)
GilaMonster (10/10/2013)
Err.. where?Ok, what are you asking? Where what? 🙂
You said
in the meantime here is the limited information from the SQL Error Log.
but I don't see anything from the error log. You mention an 'image above', but there's no image.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
try this link ...
http://i129.photobucket.com/albums/p207/jselburg1970/deadlock.png
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Eugh, traceflag 1204. I would strongly suggest you turn that off and put 1222 on in its place. Much more information...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
If the object IDs haven't changed between SQL 2005 and SQL 2008, the system procedure is sp_MSenumchangesdirect (not that it helps much)
Let's rather focus on the two objects in database 7, can you get the names of the tables and procedures? The objectID for the procedures is in the two inputbuf lines, the partitionID is listed as part of the KEY: (the part after the 7:), you can then join that to sys.indexes to get table and index names.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks Gail, it may be tomorrow or later before 1. I get the permissions and 2. the deadlock happens again.
I'll let you know as soon as I have more info.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Ok, there were multiple deadlocks over the weekend. Nearly all are fairly much the same as the log text below.
There is definitely something wrong with replication. Any thoughts, this isn't my area of experience (yet)?
Date,Source,Severity,Message
10/13/2013 11:37:10,spid15s,Unknown,waiter id=processb84898 mode=X requestType=wait
10/13/2013 11:37:10,spid15s,Unknown,waiter-list
10/13/2013 11:37:10,spid15s,Unknown,owner id=process84a3e8 mode=U
10/13/2013 11:37:10,spid15s,Unknown,owner-list
10/13/2013 11:37:10,spid15s,Unknown,keylock hobtid=72057603557294080 dbid=7 objectname=VendProd.dbo.MSmerge_genhistory indexname=nc2MSmerge_genhistory id=lock5735a8c0 mode=U associatedObjectId=72057603557294080
10/13/2013 11:37:10,spid15s,Unknown,waiter id=process84a3e8 mode=U requestType=wait
10/13/2013 11:37:10,spid15s,Unknown,waiter-list
10/13/2013 11:37:10,spid15s,Unknown,owner id=processb84898 mode=X
10/13/2013 11:37:10,spid15s,Unknown,owner-list
10/13/2013 11:37:10,spid15s,Unknown,keylock hobtid=72057603557163008 dbid=7 objectname=VendProd.dbo.MSmerge_genhistory indexname=c1MSmerge_genhistory id=lock3fef1080 mode=X associatedObjectId=72057603557163008
10/13/2013 11:37:10,spid15s,Unknown,resource-list
10/13/2013 11:37:10,spid15s,Unknown,Proc [Database Id = 32767 Object Id = 793474348]
10/13/2013 11:37:10,spid15s,Unknown,inputbuf
10/13/2013 11:37:10,spid15s,Unknown,where generation = @gen -- and guidsrc = @guidsrc
10/13/2013 11:37:10,spid15s,Unknown,changecount = @changecount
10/13/2013 11:37:10,spid15s,Unknown,coldate= getdate()<c/>
10/13/2013 11:37:10,spid15s,Unknown,art_nick = case when isnull(@art_nick<c/>0) <> 0 then @art_nick else art_nick end<c/>
10/13/2013 11:37:10,spid15s,Unknown,set genstatus = 2<c/>
10/13/2013 11:37:10,spid15s,Unknown,update dbo.MSmerge_genhistory with (rowlock)
10/13/2013 11:37:10,spid15s,Unknown,frame procname=mssqlsystemresource.sys.sp_MSupdategenhistory line=62 stmtstart=3390 stmtend=4004 sqlhandle=0x0300ff7f2c754b2f43c1ec005e9b00000100000000000000
10/13/2013 11:37:10,spid15s,Unknown,executionStack
10/13/2013 11:37:10,spid15s,Unknown,process id=processb84898 taskpriority=0 logused=376 waitresource=KEY: 7:72057603557294080 (580158c649f3) waittime=4171 ownerId=31497399 transactionname=user_transaction lasttranstarted=2013-10-13T11:37:06.737 XDES=0x163e44e0 lockMode=X schedulerid=4 kpid=7932 status=suspended spid=63 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2013-10-13T11:37:06.737 lastbatchcompleted=2013-10-13T11:37:06.727 clientapp=Replication Merge Agent hostname=[redacted] hostpid=9716 loginname=IVM-NTBIO\SQLADMIN isolationlevel=read committed (2) xactid=31497399 currentdb=7 lockTimeout=4294967295 clientoption1=536938848 clientoption2=128024
10/13/2013 11:37:10,spid15s,Unknown,Proc [Database Id = 32767 Object Id = 103774808]
10/13/2013 11:37:10,spid15s,Unknown,inputbuf
10/13/2013 11:37:10,spid15s,Unknown,inner join sys.dm_exec_sessions p on p.program_name = s.application_name collate database_default)
10/13/2013 11:37:10,spid15s,Unknown,subscriber_number not in (select s.subscriber_number from dbo.sysmergesubscriptions s
10/13/2013 11:37:10,spid15s,Unknown,coldate not in (select login_time from sys.dm_exec_sessions) and
10/13/2013 11:37:10,spid15s,Unknown,where generation <= @current_max_gen and genstatus = 4 and
10/13/2013 11:37:10,spid15s,Unknown,nicknames = @nickbin
10/13/2013 11:37:10,spid15s,Unknown,coldate = getdate()<c/>
10/13/2013 11:37:10,spid15s,Unknown,set genstatus = 3<c/>
10/13/2013 11:37:10,spid15s,Unknown,update dbo.MSmerge_genhistory with (rowlock)
10/13/2013 11:37:10,spid15s,Unknown,frame procname=mssqlsystemresource.sys.sp_MSmakegeneration line=163 stmtstart=10842 stmtend=11972 sqlhandle=0x0300ff7f587a2f063508ed005e9b00000100000000000000
10/13/2013 11:37:10,spid15s,Unknown,executionStack
10/13/2013 11:37:10,spid15s,Unknown,process id=process84a3e8 taskpriority=5 logused=916 waitresource=KEY: 7:72057603557163008 (b800f13d884e) waittime=4171 ownerId=31497398 transactionname=user_transaction lasttranstarted=2013-10-13T11:37:06.737 XDES=0x25cef600 lockMode=U schedulerid=1 kpid=4140 status=suspended spid=68 sbid=0 ecid=0 priority=-5 transcount=2 lastbatchstarted=2013-10-13T11:37:06.730 lastbatchcompleted=2013-10-13T11:37:06.727 clientapp=[redacted]\sql2005-21 hostname=[redacted] hostpid=9340 loginname=sa isolationlevel=read committed (2) xactid=31497398 currentdb=7 lockTimeout=4294967295 clientoption1=673384800 clientoption2=128024
10/13/2013 11:37:10,spid15s,Unknown,process-list
10/13/2013 11:37:10,spid15s,Unknown,deadlock victim=process84a3e8
10/13/2013 11:37:10,spid15s,Unknown,deadlock-list
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply