4

We are using SQL 2012

Our Secondary server in a log shipping configuration went offline never to come back (the VM was permanently deleted before log shipping configuration was updated).

On the Primary server, I am trying to clean up the LS configuration but everything I've tried times out and/or gives me errors about having configuration still existing.

I have done the following:

  • Deleted the LS jobs from the primary server (Backup and Alert)
  • Run the Stored Procedure "sp_delete_log_shipping_primary_secondary" with success
  • Attempted to run the Stored Procedure "sp_delete_log_shipping_primary_database", this fails.

The error with sp_delete_log_shipping_primary_database is as follows:

Msg 32011, Level 16, State 1, Procedure sp_delete_log_shipping_primary_database, Line 51 Primary Database <"DB name"> has active log shipping secondary database(s). Drop the secondary database(s) first.

If I try to use the SQL management studio GUI, it tries to get me to log into the Secondary server (no longer exists) and times out/errors when it cannot communicate with it.

How do I get log shipping configuration cleaned up on the Primary server, knowing that the Secondary one doesn't exist anymore?

I have flexibility to do a backup/restore of the database during troubleshooting, if needed.

marked as duplicate by Kin Shah, RolandoMySQLDBA, Julien Vavasseur, James Anderson, Andriy M May 25 '16 at 8:45

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 1
    I was able to resolve this by cleaning up the msdb database, from the linked/duplicate question "primary database" code: duplicate question – Elizabeth May 25 '16 at 16:48
4

I had similar error few months ago and solved by this.

USE master;
GO

EXEC master.dbo.sp_delete_log_shipping_primary_secondary
    @primary_database = N'<PrimaryDatabase>'
   ,@secondary_server = N'<SecondaryServer>'
   ,@secondary_database = N'<SecondaryDatabase>'

GO
  • I have already tried that. - bullet #2 in my original question I get errors when I try executing sp_delete_log_shipping_primary_database but I can run sp_delete_log_shipping_primary_secondary with success – Elizabeth May 25 '16 at 16:39

Not the answer you're looking for? Browse other questions tagged or ask your own question.