Violation of PRIMARY KEY constraint in Log Shipping

·         I have SQL Server 2005 log shipping setup with primary/secondary configuration.  I can confirm from the logs that log shipping is working without issue, however, reports generated from the monitor server show this message:

Violation of PRIMARY KEY constraint 'PK__#log_shipping_mo__3ABBDC91'. Cannot insert duplicate key in object 'dbo.#log_shipping_monitor'.  The statement has been terminated.

There is nothing special about the configuration.  Any ideas?

·         I think the problem you are seeing is related to some old information being present in the tables used to store log shipping configuration.  There are some scenarios where this can happen and it causes the problem you reported in your first post.  We are working on correcting this in a future release.

 As you can tell from the error, the problem is caused by an insert to a temp table causing a PK constraint violation.  The PK for the temp table is server name and database name.  This error is normally caused by old configuration being present in the tables log_shipping_monitor_primary and/or log_shipping_monitor_secondary.  You can view the contents of these tables directly (in msdb) or use some supplied help SP's (see BOL topic titled "Log Shipping Tables and Stored Procedures").

 The workaround is to remove the old rows from log_shipping_monitor_primary and/or log_shipping_monitor_secondary tables.  Can you determine that you do indeed have stale data in the log shipping tables.  If this is the case I can work with you on how to remove the old rows.

 The old configuration data is probably causing the incorrect alerts you are seeing.

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.”