log shipping monitor report error

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?

Clear out the log on Log shipping monitor and ensure the log shipping is working properly on secondary server too.|||Log shipping is working properly on the secondary server. The error is generated when I try to view the Log Shipping Status report on the secondary server. The monitor server instance is also throwing incorrect alerts.|||

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.

|||

Hi, Is that possible for you to show how to delete the old data in the tables log_shipping_monitor_primary and/or log_shipping_monitor_secondary.

Thank you

|||

Hi Mark,

I am getting a similar error when I try to run the report on the primary server. The report on the secondary server which is also the monitor runs fine.

I queried the tables mentioned by you and got one row in each. FYI, I have only one DB on the primary server being log shipped to the secondary.

For,

select * from dbo.log_shipping_monitor_secondary

I am geeting null values for last_copied_file, last_copied_date, last_copied_date_utc and last restored_file. However, I know that the log shipping is working well for the database.

So How do I go about correcting the report? Should I delete the row in log_shipping_monitor_secondary? In log_shipping_monitor_primary the information is up to date and correct.

Thanks in anticipation.

Amit