Primary Database xxx has active log shipping secondary database(s). Drop the secondary database(s) first.

Ran into a bit of a glitch with Log Shipping today.

As you know, there are three simple documented steps to remove log shipping from your server:

  1. Remove log shipping on primary using :

    EXEC master.dbo.sp_delete_log_shipping_primary_secondary

  2. Remove log shipping on secondary usingsp_delete_log_shipping_secondary_database
  3. Remove log shipping on primary using : sp_delete_log_shipping_primary_database

 

Now, there was a problem here. The second step should ideally drop the database, but it didnt. So when it came to the 3rd step, it threw up this error :

Primary Database xxx has active log shipping secondary database(s). Drop the secondary database(s) first.

So the secondary database was dropped and this command was executed again. It still failed. Something was not right.

The issue is that the information about this database still exists in a system table. You need to query that and remove the record:

select * from msdb.dbo.log_shipping_primary_secondaries

Once you delete the record, you can safely run the 3rd step and it will succeed.

 

 

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s