September 1, 2010 at 9:12 am
Hi experts
I have a situation where a log shipping alert keeps reporting a failure due to a database being out of sync and having no log restored for a given time.
The problem is, the database in question does not exist anymore on the primary or secondary server, but I can see it in the MSDB log shipping tables on the secondary server. I assume the previous DBA had dropped it prior to disabling the log shipping?
So my quesiton is, can I simply remove the entries in the following tables:
[MSDB].[dbo].[log_shipping_secondary_databases]
[MSDB].[dbo].[log_shipping_secondary]
[MSDB].[dbo].[log_shipping_monitor_secondary]
In order for the log shipping alert to no longer report an error, as everything else is running fine except for these "ghost" db's?
Thanks in advance
Nick
September 1, 2010 at 10:59 pm
You may find this information useful.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
September 2, 2010 at 2:29 am
Excellent. It was the [sp_delete_log_shipping_secondary_database] sp which I needed.
Thanks
Nick
September 2, 2010 at 4:58 am
To Remove Log Shipping
1.On the primary server, execute sp_delete_log_shipping_primary_secondary to delete the information about the secondary database from the primary server.
2. On the secondary server, execute sp_delete_log_shipping_secondary_database to delete the secondary database.( If there are no other secondary databases with the same secondary ID, sp_delete_log_shipping_secondary_primary is invoked from sp_delete_log_shipping_secondary_database and deletes the entry for the secondary ID and the copy and restore jobs.)
3. On the primary server, execute sp_delete_log_shipping_primary_database to delete information about the log shipping configuration from the primary server. This also deletes the backup job.
4. Delete the secondary database from the secondary server if desired.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply