sp_help_log_shipping_monitor may return the following error after role reversal in SQL 2000/2005 : Violation of PRIMARY KEY constraint <constraint name>. Cannot insert duplicate key in object ‘dbo.#log_shipping_monitor’


Problem Description


===============


·         After role reversal in a log shipping setup, the stored procedure sp_help_log_shipping_monitor may return the following error OR we may find the error in the log shipping report in SQL Server Management Studio.


Msg 2627, Level 14, State 1, Procedure sp_help_log_shipping_monitor, Line 148


Violation of PRIMARY KEY constraint ‘PK__#log_shipping_mo__15502E78’. Cannot insert duplicate key in object ‘dbo.#log_shipping_monitor’.


The statement has been terminated.


 


·         The stored procedure sp_help_log_shipping_monitor fetches data from log_shipping_monitor_primary and log_shipping_monitor_secondary tables and puts them into a temp table called #log_shipping_monitor. This temp table has a primary key with the ServerName and the DatabaseName.


log_shipping_monitor_primary – Stores one monitor record per primary database in each log shipping configuration


log_shipping_monitor_secondary – Stores one monitor record per secondary database in a log shipping configuration


 


·         The problem may happen under few scenarios such as:


1.       A manual failover was done for the log shipped database. Since, the roles are not switched on the monitor server, there would be two entries in the Log_shipping_monitor_secondary table. This would cause the stored procedure sp_help_log_shipping_monitor to fail.


 


2.       There could be incorrect metadata cleanup of a previous log shipping setup for the same log shipped database.


 


Resolution


========
Note: Any incorrect update/deletion in the metadata table may lead to inconsistencies in the logshipping setup,


 


Let us say :


Logshipping Database name : TESTDB


Primary Server name : Server\Primary


Secondary Server name : Server\Secondary


 


Check the entries under –


 


1) select primary_server, primary_database from db.dbo.log_shipping_monitor_primary  –


 


Output :


primary_server     primary_database


—————— —————-


Server\Primary         TestDB


 


Primary server should have an entry corresponding to its primary database. It should show Primary_server as Server\Primary and Primary_database as TESTDB


 


2) select secondary_server,secondary_database,primary_server, primary_database from msdb.dbo.log_shipping_monitor_secondary –


 


 Output:


secondary_server     secondary_database  primary_server   primary_database


——————– ——————- —————- —————–


Server\Secondary                TestDB            Server\Primary    TestDB


 


Secondary server should have a correct entry corresponding to it’s primary server and primary database.


It should show Secondary_server as Server\Secondary, secondary_database as TESTDB, primary_server as Server\Primary, Primary_database as TESTDB


 


Now if you find any mismatch in the above output, you need to update/delete the incorrect entries manually using Delete or Update command depending on the scenario.


 


For example –


 


Scenario 1 :


select secondary_server,secondary_database,primary_server, primary_database from msdb.dbo.log_shipping_monitor_secondary –


 


Output:


secondary_server     secondary_database  primary_server   primary_database


——————– ——————- —————- —————–


Server\Secondary                TestDB            Server\other_server    TestDB


 


The output here shows incorrect server under Primary_Server column, To resolve this update the msdb.dbo.log_shipping_monitor_secondary manually as –


 


Update  msdb.dbo.log_shipping_monitor_secondary Set Primary_server = ‘Server\Primary’ where Primary_server = ‘Server\Other_server’


 


 


Scenario 2 :


select secondary_server,secondary_database,primary_server, primary_database from msdb.dbo.log_shipping_monitor_secondary –


 


Output:


secondary_server     secondary_database  primary_server   primary_database


——————– ——————- —————- —————–


Server\Secondary                TestDB            Server\Primary          TestDB


Server\Secondary                TestDB            Server\other_server     TestDB


 


In this scenario, It shows uncleaned metadata which might be from previous Log shipping setup so delete the entry from msdb.dbo.log_shipping_monitor_secondary as –


 


Delete from  msdb.dbo.log_shipping_monitor_secondary where Primary_server = ‘Server\Other_server’


 


Note: “There was a design change that was done on SQL Server 2008 to prevent this issue from occurring.” 


 


Deepak Saluja
SE, Microsoft SQL server


Reviewed by
Shamik Ghosh
Technical Lead, Microsoft SQL Server.
Amit Banerjee
Support Escalation Engineer , Microsoft SQL server


 

Comments (0)