May 19, 2018 at 12:43 am
My Log shipping is working normally my Production and DR servers are synchronized but As I have a deadlock notification enabled, since 3 days I saw deadlock occur.
My Server Is SQL SERVER 2016 SP1 Standard edition.
We attempted the following solution but the deadlock still happen:
1. Killing the SPID involved in the deadlock.
2. Cleaning Up msdb.dbo.log_shipping_monitor_history_detail and msdb.dbo.log_shipping_monitor_history_detail and error detail
3. Recreate LogShipping and disable the monitor server.
All the above-attempted solution does not work.
DEADLOCK REPORT :
deadlock-list>
<deadlock victim="process73b5063848">
<process-list>
<process id="process73b5063848" taskpriority="0" logused="10000" waittime="109" schedulerid="10" kpid="5768" status="suspended" spid="172" sbid="1" ecid="7" priority="0" trancount="0" lastbatchstarted="2018-05-16T09:20:01.087" lastbatchcompleted="2018-05-16T09:20:01.097" lastattention="1900-01-01T00:00:00.097" clientapp="Microsoft SQL Server" hostname="XXXXXXX" hostpid="1548" isolationlevel="read committed (2)" xactid="5435990489" currentdb="4" currentdbname="msdb" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
<executionStack>
<frame procname="mssqlsystemresource.sys.sp_MSprocesslogshippingretentioncleanup" line="23" stmtstart="1264" stmtend="1606" sqlhandle="0x0300ff7f34646cf4bc04b700a7a8000001000000000000000000000000000000000000000000000000000000">
delete from msdb.dbo.log_shipping_monitor_history_detail
where agent_id = @agent_id
and agent_type = @agent_type
and log_time_utc < @cutoff_time_ut </frame>
<frame procname="mssqlsystemresource.sys.sp_processlogshippingretentioncleanup" line="37" stmtstart="1910" stmtend="2740" sqlhandle="0x0300ff7ffc98dfd060d3b600a7a8000001000000000000000000000000000000000000000000000000000000">
exec @retcode = sys.sp_MSprocesslogshippingretentioncleanup
@agent_id = @agent_id
,@agent_type = @agent_type
,@monitor_server = @monitor_server
,@monitor_server_security_mode = @monitor_server_security_mode
,@history_retention_period = @history_retention_period
,@curdate_utc = @curdate_ut </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 32767 Object Id = -790652676] </inputbuf>
</process>
<process id="process7c616c08c8" taskpriority="0" logused="10000" waittime="46029" schedulerid="14" kpid="12488" status="suspended" spid="172" sbid="1" ecid="8" priority="0" trancount="0" lastbatchstarted="2018-05-16T09:20:01.087" lastbatchcompleted="2018-05-16T09:20:01.097" lastattention="1900-01-01T00:00:00.097" clientapp="Microsoft SQL Server" hostname="XXXXXXX" hostpid="1548" isolationlevel="read committed (2)" xactid="5435990489" currentdb="4" currentdbname="msdb" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
<executionStack>
<frame procname="mssqlsystemresource.sys.sp_MSprocesslogshippingretentioncleanup" line="23" stmtstart="1264" stmtend="1606" sqlhandle="0x0300ff7f34646cf4bc04b700a7a8000001000000000000000000000000000000000000000000000000000000">
delete from msdb.dbo.log_shipping_monitor_history_detail
where agent_id = @agent_id
and agent_type = @agent_type
and log_time_utc < @cutoff_time_ut </frame>
<frame procname="mssqlsystemresource.sys.sp_processlogshippingretentioncleanup" line="37" stmtstart="1910" stmtend="2740" sqlhandle="0x0300ff7ffc98dfd060d3b600a7a8000001000000000000000000000000000000000000000000000000000000">
exec @retcode = sys.sp_MSprocesslogshippingretentioncleanup
@agent_id = @agent_id
,@agent_type = @agent_type
,@monitor_server = @monitor_server
,@monitor_server_security_mode = @monitor_server_security_mode
,@history_retention_period = @history_retention_period
,@curdate_utc = @curdate_ut </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 32767 Object Id = -790652676] </inputbuf>
</process>
<process id="process7c3f920ca8" taskpriority="0" logused="10000" waittime="46028" schedulerid="9" kpid="1164" status="suspended" spid="172" sbid="1" ecid="6" priority="0" trancount="0" lastbatchstarted="2018-05-16T09:20:01.087" lastbatchcompleted="2018-05-16T09:20:01.097" lastattention="1900-01-01T00:00:00.097" clientapp="Microsoft SQL Server" hostname="XXXXXXX" hostpid="1548" isolationlevel="read committed (2)" xactid="5435990489" currentdb="4" currentdbname="msdb" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
<executionStack>
<frame procname="mssqlsystemresource.sys.sp_MSprocesslogshippingretentioncleanup" line="23" stmtstart="1264" stmtend="1606" sqlhandle="0x0300ff7f34646cf4bc04b700a7a8000001000000000000000000000000000000000000000000000000000000">
delete from msdb.dbo.log_shipping_monitor_history_detail
where agent_id = @agent_id
and agent_type = @agent_type
and log_time_utc < @cutoff_time_ut </frame>
<frame procname="mssqlsystemresource.sys.sp_processlogshippingretentioncleanup" line="37" stmtstart="1910" stmtend="2740" sqlhandle="0x0300ff7ffc98dfd060d3b600a7a8000001000000000000000000000000000000000000000000000000000000">
exec @retcode = sys.sp_MSprocesslogshippingretentioncleanup
@agent_id = @agent_id
,@agent_type = @agent_type
,@monitor_server = @monitor_server
,@monitor_server_security_mode = @monitor_server_security_mode
,@history_retention_period = @history_retention_period
,@curdate_utc = @curdate_ut </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 32767 Object Id = -790652676] </inputbuf>
</process>
<process id="process7a0f16e108" taskpriority="0" logused="10000" waittime="46028" schedulerid="12" kpid="10044" status="suspended" spid="172" sbid="1" ecid="1" priority="0" trancount="0" lastbatchstarted="2018-05-16T09:20:01.087" lastbatchcompleted="2018-05-16T09:20:01.097" lastattention="1900-01-01T00:00:00.097" clientapp="Microsoft SQL Server" hostname="XXXXXXX" hostpid="1548" isolationlevel="read committed (2)" xactid="5435990489" currentdb="4" currentdbname="msdb" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
<executionStack>
<frame procname="mssqlsystemresource.sys.sp_MSprocesslogshippingretentioncleanup" line="23" stmtstart="1264" stmtend="1606" sqlhandle="0x0300ff7f34646cf4bc04b700a7a8000001000000000000000000000000000000000000000000000000000000">
delete from msdb.dbo.log_shipping_monitor_history_detail
where agent_id = @agent_id
and agent_type = @agent_type
and log_time_utc < @cutoff_time_ut </frame>
<frame procname="mssqlsystemresource.sys.sp_processlogshippingretentioncleanup" line="37" stmtstart="1910" stmtend="2740" sqlhandle="0x0300ff7ffc98dfd060d3b600a7a8000001000000000000000000000000000000000000000000000000000000">
exec @retcode = sys.sp_MSprocesslogshippingretentioncleanup
@agent_id = @agent_id
,@agent_type = @agent_type
,@monitor_server = @monitor_server
,@monitor_server_security_mode = @monitor_server_security_mode
,@history_retention_period = @history_retention_period
,@curdate_utc = @curdate_ut </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 32767 Object Id = -790652676] </inputbuf>
</process>
<process id="process73b5036108" taskpriority="0" logused="10000" waittime="46029" schedulerid="16" kpid="10776" status="suspended" spid="172" sbid="1" ecid="2" priority="0" trancount="0" lastbatchstarted="2018-05-16T09:20:01.087" lastbatchcompleted="2018-05-16T09:20:01.097" lastattention="1900-01-01T00:00:00.097" clientapp="Microsoft SQL Server" hostname="XXXXXXX" hostpid="1548" isolationlevel="read committed (2)" xactid="5435990489" currentdb="4" currentdbname="msdb" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
<executionStack>
<frame procname="mssqlsystemresource.sys.sp_MSprocesslogshippingretentioncleanup" line="23" stmtstart="1264" stmtend="1606" sqlhandle="0x0300ff7f34646cf4bc04b700a7a8000001000000000000000000000000000000000000000000000000000000">
delete from msdb.dbo.log_shipping_monitor_history_detail
where agent_id = @agent_id
and agent_type = @agent_type
and log_time_utc < @cutoff_time_ut </frame>
<frame procname="mssqlsystemresource.sys.sp_processlogshippingretentioncleanup" line="37" stmtstart="1910" stmtend="2740" sqlhandle="0x0300ff7ffc98dfd060d3b600a7a8000001000000000000000000000000000000000000000000000000000000">
exec @retcode = sys.sp_MSprocesslogshippingretentioncleanup
@agent_id = @agent_id
,@agent_type = @agent_type
,@monitor_server = @monitor_server
,@monitor_server_security_mode = @monitor_server_security_mode
,@history_retention_period = @history_retention_period
,@curdate_utc = @curdate_ut </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 32767 Object Id = -790652676] </inputbuf>
</process>
<process id="process7c52525468" taskpriority="0" logused="0" waitresource="PAGE: 4:1:32351 " waittime="46061" ownerId="5435990489" transactionname="DELETE" lasttranstarted="2018-05-16T09:20:01.087" XDES="0x7c5f411c40" lockMode="U" schedulerid="13" kpid="10912" status="suspended" spid="172" sbid="1" ecid="4" priority="0" trancount="0" lastbatchstarted="2018-05-16T09:20:01.087" lastbatchcompleted="2018-05-16T09:20:01.097" lastattention="1900-01-01T00:00:00.097" clientapp="Microsoft SQL Server" hostname="XXXXXXX" hostpid="1548" isolationlevel="read committed (2)" xactid="5435990489" currentdb="4" currentdbname="msdb" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
<executionStack>
<frame procname="mssqlsystemresource.sys.sp_MSprocesslogshippingretentioncleanup" line="23" stmtstart="1264" stmtend="1606" sqlhandle="0x0300ff7f34646cf4bc04b700a7a8000001000000000000000000000000000000000000000000000000000000">
delete from msdb.dbo.log_shipping_monitor_history_detail
where agent_id = @agent_id
and agent_type = @agent_type
and log_time_utc < @cutoff_time_ut </frame>
<frame procname="mssqlsystemresource.sys.sp_processlogshippingretentioncleanup" line="37" stmtstart="1910" stmtend="2740" sqlhandle="0x0300ff7ffc98dfd060d3b600a7a8000001000000000000000000000000000000000000000000000000000000">
exec @retcode = sys.sp_MSprocesslogshippingretentioncleanup
@agent_id = @agent_id
,@agent_type = @agent_type
,@monitor_server = @monitor_server
,@monitor_server_security_mode = @monitor_server_security_mode
,@history_retention_period = @history_retention_period
,@curdate_utc = @curdate_ut </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 32767 Object Id = -790652676] </inputbuf>
</process>
<process id="process7428818ca8" taskpriority="0" logused="0" waitresource="PAGE: 4:1:32351 " waittime="46068" ownerId="5435990537" transactionname="DELETE" lasttranstarted="2018-05-16T09:20:01.093" XDES="0x7c602e9c40" lockMode="U" schedulerid="3" kpid="7144" status="suspended" spid="195" sbid="1" ecid="7" priority="0" trancount="0" lastbatchstarted="2018-05-16T09:20:01.093" lastbatchcompleted="2018-05-16T09:20:01.097" lastattention="1900-01-01T00:00:00.097" clientapp="Microsoft SQL Server" hostname="XXXXXXX" hostpid="1548" isolationlevel="read committed (2)" xactid="5435990537" currentdb="4" currentdbname="msdb" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
<executionStack>
<frame procname="mssqlsystemresource.sys.sp_MSprocesslogshippingretentioncleanup" line="23" stmtstart="1264" stmtend="1606" sqlhandle="0x0300ff7f34646cf4bc04b700a7a8000001000000000000000000000000000000000000000000000000000000">
delete from msdb.dbo.log_shipping_monitor_history_detail
where agent_id = @agent_id
and agent_type = @agent_type
and log_time_utc < @cutoff_time_ut </frame>
<frame procname="mssqlsystemresource.sys.sp_processlogshippingretentioncleanup" line="37" stmtstart="1910" stmtend="2740" sqlhandle="0x0300ff7ffc98dfd060d3b600a7a8000001000000000000000000000000000000000000000000000000000000">
exec @retcode = sys.sp_MSprocesslogshippingretentioncleanup
@agent_id = @agent_id
,@agent_type = @agent_type
,@monitor_server = @monitor_server
,@monitor_server_security_mode = @monitor_server_security_mode
,@history_retention_period = @history_retention_period
,@curdate_utc = @curdate_ut </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 32767 Object Id = -790652676] </inputbuf>
</process>
<process id="process7c6102f468" taskpriority="0" logused="10000" waittime="46075" schedulerid="3" kpid="7244" status="suspended" spid="172" sbid="1" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-05-16T09:20:01.087" lastbatchcompleted="2018-05-16T09:20:01.097" lastattention="2018-05-16T09:10:05.673" clientapp="Microsoft SQL Server" hostname="XXXXXXX" hostpid="1548" loginname="WEB_ROLLOVER\svc_agent_sql" isolationlevel="read committed (2)" xactid="5435990489" currentdb="4" currentdbname="msdb" lockTimeout="4294967295" clientoption1="538968096" clientoption2="128056">
<executionStack>
<frame procname="mssqlsystemresource.sys.sp_MSprocesslogshippingretentioncleanup" line="23" stmtstart="1264" stmtend="1606" sqlhandle="0x0300ff7f34646cf4bc04b700a7a8000001000000000000000000000000000000000000000000000000000000">
delete from msdb.dbo.log_shipping_monitor_history_detail
where agent_id = @agent_id
and agent_type = @agent_type
and log_time_utc < @cutoff_time_ut </frame>
<frame procname="mssqlsystemresource.sys.sp_processlogshippingretentioncleanup" line="37" stmtstart="1910" stmtend="2740" sqlhandle="0x0300ff7ffc98dfd060d3b600a7a8000001000000000000000000000000000000000000000000000000000000">
exec @retcode = sys.sp_MSprocesslogshippingretentioncleanup
@agent_id = @agent_id
,@agent_type = @agent_type
,@monitor_server = @monitor_server
,@monitor_server_security_mode = @monitor_server_security_mode
,@history_retention_period = @history_retention_period
,@curdate_utc = @curdate_ut </frame>
</executionStack>
<inputbuf>
Proc [Database Id = 32767 Object Id = -790652676] </inputbuf>
</process>
</process-list>
<resource-list>
<exchangeEvent id="Port7c5b7d7700" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="1" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
<owner-list>
<owner id="process73b5036108" />
</owner-list>
<waiter-list>
<waiter id="process73b5063848" />
</waiter-list>
</exchangeEvent>
<exchangeEvent id="Port7c5b7d7700" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="8" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
<owner-list>
<owner id="process7c6102f468" />
</owner-list>
<waiter-list>
<waiter id="process7c616c08c8" />
</waiter-list>
</exchangeEvent>
<exchangeEvent id="Port7c5b7d7700" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="7" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
<owner-list>
<owner id="process7c616c08c8" />
</owner-list>
<waiter-list>
<waiter id="process7c3f920ca8" />
</waiter-list>
</exchangeEvent>
<exchangeEvent id="Port7c5b7d7700" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="6" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
<owner-list>
<owner id="process7c3f920ca8" />
</owner-list>
<waiter-list>
<waiter id="process7a0f16e108" />
</waiter-list>
</exchangeEvent>
<exchangeEvent id="Port7c5b7d7700" WaitType="e_waitPortOpen" waiterType="Producer" nodeId="2" tid="2" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
<owner-list>
<owner id="process7c52525468" />
</owner-list>
<waiter-list>
<waiter id="process73b5036108" />
</waiter-list>
</exchangeEvent>
<pagelock fileid="1" pageid="32351" dbid="4" subresource="FULL" objectname="msdb.dbo.log_shipping_monitor_history_detail" id="lock6c1f59d600" mode="U" associatedObjectId="72057594051559424">
<owner-list>
<owner id="process7428818ca8" mode="U" requestType="wait" />
</owner-list>
<waiter-list>
<waiter id="process7c52525468" mode="U" requestType="wait" />
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="32351" dbid="4" subresource="FULL" objectname="msdb.dbo.log_shipping_monitor_history_detail" id="lock6c1f59d600" mode="U" associatedObjectId="72057594051559424">
<owner-list>
<owner id="process7a0f16e108" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process7428818ca8" mode="U" requestType="wait" />
</waiter-list>
</pagelock>
<exchangeEvent id="Port7c5b7d7700" WaitType="e_waitPortOpen" waiterType="Coordinator" nodeId="2" tid="0" ownerActivity="notYetOpened" waiterActivity="waitForAllOwnersToOpen">
<owner-list>
<owner id="process73b5063848" />
</owner-list>
<waiter-list>
<waiter id="process7c6102f468" />
</waiter-list>
</exchangeEvent>
</resource-list>
</deadlock>
</deadlock-list>
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply