March 10, 2009 at 11:10 am
Hello,
We had a problem with our log shipping and reset the configuration. This all seemed OK until we were getting out of sync errors so reset the configuration again. This again all seemed to be fine and the secondary databases are now in sync. However, we are now unable to open the log shipping stauts report on the primary/monitor server getting the "cannot insert duplicate key in object dbo.#log_shipping_monitor" error but this report works fine on the secondary server
When i run "select * from log_shipping_monitor_secondary" on the secondary server i get the correct table results....but running it on the primary/monitor server i get the following results:
secondary_serversecondary_databasesecondary_idprimary_serverprimary_database
SYS2 DeviceWall 6903E98C-47B5-4562-B570-6C2D20682677SYS2DeviceWall
SYS2 Discovery B725DC3B-CAE6-4FEA-B2E4-A0A5FAB57EC0SYS2Discovery
SYS4\LOGSHIPDiscovery D29E1943-F81C-4F54-9006-A7D2FBBBE5ADSYS2Discovery
SYS4\LOGSHIPDeviceWall DBAB13CB-08A5-43FA-9F8E-FFD4D6E2E41FSYS2DeviceWall
Running the "select * from log_shipping_monitor_primary" command shows the correct table entries. From the results it looks like the databases tried to log ship back to the primary server (is this even possible....and how did sql accept this configuration??).
I have run all of the sp's to cleanup or delete the log shipping history and to refresh the monitor on both servers which completed successfully....but the problem still exists. I know the tables are protected and are not editable but i cannot find any way to delete these entries and we are receiving emails from our monitoring software constantly about these being our of sync even though there is no secondary database to restore to!!
There are a number of other databases on this server that log ship and as this is a production server we cannot restore the MSDB (unless absolutely necessary!!).
Can anyone help or does Microsoft have a way to edit these tables in certain curcumstances??
Thanks in advance
Samuel
March 10, 2009 at 1:38 pm
Do this at ur own risk:-
There IS a way though. You can put the SERVER into single-user mode, then connect with the DAC and you can then update the system tables.
MJ
March 10, 2009 at 1:58 pm
thanks MJ....ill discuss this with the others and see if they are happy to do this
March 17, 2009 at 3:21 pm
Is your issue resolved by taking DAC connection?
MJ
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply