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

  • Article

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