Log Shipping Issue (encountered by Abi Chapagai May 7th)
I had set up log shipping between two servers few weeks ago. Monitor server is the primary server in my set up. Log shipping was running fine but when I checked the log shipping status today from the primary and secondary servers I got following error message:
Violation of PRIMARY KEY constraint ‘PK_#log_shipping_mo__2BFE89A6′. Cannot insertduplicate key in object ‘dbo.#log_shipping_monitor’. The statement was terminated.
This was strange but I figured this out. The issue was caused by old configuration data in the primary and secondary server.
To solve this issue, I did check on log_shipping_monitor_primary and table log_shipping_monitor_secondary tables on MSDB database. SQL statement to check are:
select * from log_shipping_monitor_primary
select * from log_shipping_monitor_secondary
I found the double entry on primary_id column on log_shipping_monitor_primary table, I deleted the old entry using the following statement on log_shipping_monitor_primary table:
delete from log_shipping_monitor_primary where primary_id = ‘325BE9F8-CDA8-4E2A-AFA9-E33B7002EE2B’ — This is an old record on primary server.
I found the double entry on primary_id column on log_shipping_monitor_secondary table; I deleted the old entry using the following statement on log_shipping_monitor_ secondary table:
delete from log_shipping_monitor_secondary where secondary_id = ‘258DC9AF-65E8-42F6-9183′ —-This is an old record on secondary server.
Once I deleted them, I did not get this Alert error in log shipping status. Let me know if any of you have gone through this issue and share your solution.