Thanks guys, I've managed to capture a deadlock using SQL Profiler and the graph is below.
Additionally the deadlock XML output is below
<deadlock-list>
<deadlock victim="process769288">
<process-list>
<process id="process769288" taskpriority="0" logused="1476" waitresource="KEY: 8:72057594111983616 (47c4361ec2ad)" waittime="484" ownerId="273355729" transactionname="user_transaction" lasttranstarted="2013-10-08T21:03:59.523" XDES="0x10b357270" lockMode="U" schedulerid="2" kpid="14220" status="suspended" spid="147" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-10-08T21:03:59.527" lastbatchcompleted="2013-10-08T21:03:59.527" clientapp="SMS_DISTRIBUTION_MANAGER" hostname="SHDEPLOY2012" hostpid="4004" loginname="NT AUTHORITY\SYSTEM" isolationlevel="read committed (2)" xactid="273355729" currentdb="8" lockTimeout="4294967295" clientoption1="671287392" clientoption2="128056">
<executionStack>
<frame procname="CM_LAB.dbo.spUpdateSiteControl" line="12" stmtstart="622" stmtend="1400" sqlhandle="0x0300080041e141055d1bc40049a200000100000000000000">
MERGE INTO SiteControl AS T
USING (
SELECT @SiteCode AS SiteCode, GETUTCDATE() AS DateLastModified
) AS S(SiteCode, DateLastModified)
ON S.SiteCode = T.SiteCode
WHEN MATCHED THEN
UPDATE SET T.DateLastModified = S.DateLastModified
WHEN NOT MATCHED THEN
INSERT (SiteCode, DateLastModified)
VALUES(S.SiteCode, S.DateLastModified); </frame>
<frame procname="CM_LAB.dbo.tr_SC_SysResUse_Property_ins_upd_del" line="22" stmtstart="1594" stmtend="1672" sqlhandle="0x03000800a1e81749c924c40049a200000000000000000000">
EXEC spUpdateSiteControl @SiteCode </frame>
<frame procname="CM_LAB.dbo.tr_vSMS_SysResUse_Properties_upd" line="12" stmtstart="728" stmtend="1374" sqlhandle="0x03000800c63fcf7aa431c40049a200000000000000000000">
update rel
set rel.Value1=ins.Value1,
rel.Value2=ins.Value2,
rel.Value3=ins.Value3
from SC_SysResUse_Property as rel
inner join inserted as ins on rel.Name=ins.Name and rel.SysResUseID=ins.ID
where rel.Value1!=ins.Value1 or rel.Value2!=ins.Value2 or rel.Value3!=ins.Value3 </frame>
<frame procname="adhoc" line="3" stmtstart="228" sqlhandle="0x02000000a88e68210a5e17dbd208c883c5925dc10fd661eb">
UPDATE [vSMS_SC_SysResUse_Properties] set [ID] = @1,[Name] = @2,[Value1] = @3,[Value2] = @4,[Value3] = @5 WHERE [ID]=@6 AND [Name]=@7 </frame>
<frame procname="adhoc" line="3" stmtstart="586" stmtend="1026" sqlhandle="0x0200000089cc65023756c15d75189d92931f875bffb8980b">
update vSMS_SC_SysResUse_Properties set ID = 72057594037927953, Name = N'AvailableContentLibDrivesList', Value1 = N'DDC', Value2 = N'', Value3 = 0 where ID = 72057594037927953 and Name = N'AvailableContentLibDrivesList' </frame>
</executionStack>
<inputbuf>
IF NOT EXISTS (select 1 from vSMS_SC_SysResUse_Properties where ID = 72057594037927953 and Name = N'AvailableContentLibDrivesList'
) insert into vSMS_SC_SysResUse_Properties (ID, Name, Value1, Value2, Value3) values (72057594037927953, N'AvailableContentLibDrivesList', N'DDC', N'', 0)
ELSE update vSMS_SC_SysResUse_Properties set ID = 72057594037927953, Name = N'AvailableContentLibDrivesList', Value1 = N'DDC', Value2 = N'', Value3 = 0 where ID = 72057594037927953 and Name = N'AvailableContentLibDrivesList'
</inputbuf>
</process>
<process id="process8fa5a748" taskpriority="0" logused="3160" waitresource="KEY: 8:72057595436728320 (d51fef0ca10c)" waittime="206" ownerId="273355664" transactionname="user_transaction" lasttranstarted="2013-10-08T21:03:59.493" XDES="0x12b176e90" lockMode="U" schedulerid="4" kpid="3812" status="suspended" spid="102" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-10-08T21:03:59.800" lastbatchcompleted="2013-10-08T21:03:59.800" clientapp="SMS_DISTRIBUTION_MANAGER" hostname="SHDEPLOY2012" hostpid="4004" loginname="NT AUTHORITY\SYSTEM" isolationlevel="read committed (2)" xactid="273355664" currentdb="8" lockTimeout="4294967295" clientoption1="671156320" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" sqlhandle="0x02000000dd107007806e2488472b44e8e2cb235ae39d3ffb">
delete [vSMS_SC_SysResUse_Properties] where ID=72057594037927955 and Name not in (N'ADSiteName', N'AllowInternetClients', N'AvailableContentLibDrivesList', N'AvailablePkgShareDrivesList', N'BindPolicy', N'BITS download', N'CertificateContextData', N'CertificateExpirationDate', N'CertificateFile', N'CertificatePFXData', N'CertificateType', N'DPDrive', N'DPMonEnabled', N'DPMonPriority', N'DPMonSchedule', N'DPShareDrive', N'IdentityGUID', N'IISConfigCheckTimeInterval', N'InstallInternetServer', N'IPSubnets', N'IPv6Prefixes', N'IsActive', N'IsAnonymousEnabled', N'IsMulticast', N'IsPXE', N'LastIISConfigCheckTime', N'MinFreeSpace', N'PreStagingAllowed', N'PXEPassword', N'RemoveWDS', N'ResponseDelay', N'Server Remote Name', N'Server Remote Public Name', N'SslState', N'SupportUnknownMachines', N'UdaSetting') </frame>
</executionStack>
<inputbuf>
delete [vSMS_SC_SysResUse_Properties] where ID=72057594037927955 and Name not in (N'ADSiteName', N'AllowInternetClients', N'AvailableContentLibDrivesList', N'AvailablePkgShareDrivesList', N'BindPolicy', N'BITS download', N'CertificateContextData', N'CertificateExpirationDate', N'CertificateFile', N'CertificatePFXData', N'CertificateType', N'DPDrive', N'DPMonEnabled', N'DPMonPriority', N'DPMonSchedule', N'DPShareDrive', N'IdentityGUID', N'IISConfigCheckTimeInterval', N'InstallInternetServer', N'IPSubnets', N'IPv6Prefixes', N'IsActive', N'IsAnonymousEnabled', N'IsMulticast', N'IsPXE', N'LastIISConfigCheckTime', N'MinFreeSpace', N'PreStagingAllowed', N'PXEPassword', N'RemoveWDS', N'ResponseDelay', N'Server Remote Name', N'Server Remote Public Name', N'SslState', N'SupportUnknownMachines', N'UdaSetting') </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594111983616" dbid="8" objectname="CM_LAB.dbo.SiteControl" indexname="SiteControl_PK" id="lockf8de5880" mode="X" associatedObjectId="72057594111983616">
<owner-list>
<owner id="process8fa5a748" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process769288" mode="U" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057595436728320" dbid="8" objectname="CM_LAB.dbo.SC_SysResUse_Property" indexname="SC_SysResUse_Property_AK" id="lock187f4f680" mode="U" associatedObjectId="72057595436728320">
<owner-list>
<owner id="process769288" mode="U"/>
</owner-list>
<waiter-list>
<waiter id="process8fa5a748" mode="U" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
Any ideas? Again this only occasionally happens when content is updated on the distribution points.
Thanks
Sam
-
Edited by
slewis1310
Tuesday, October 08, 2013 10:31 PM
Other recent topics
