0

Working with a server running SQL Server that was the log shipping target for two other servers as part of server consolidation/migration. In other words, server B was log shipping to server A and server C was log shipping to server A and the we failed over both to A and retired B and C.

Server A is happily chugging along. All the old log shipping related jobs were paused and deleted long ago.

Enter a new DB monitoring tool we are trialing (Spotlight Cloud) that throws warnings about our server being a secondary with a high restore latency (2 years! lol). Now, I know this isn't really an issue (afaik) but it does cause me to wonder -- what step did I skip in those failovers?

I still have entries in dbo.log_shipping_secondary. Should I? I have looked through SQL Docs online and don't really see anything covering this scenario as far as final steps.

Improve this question

1 Answer 1

Reset to default
3

You need to remove the log shipping metadata. The correct way to do this is via stored procedures, which also removes the jobs (I'm guessing you've deleted those manually).

  1. Execute sp_delete_log_shipping_secondary_primary. This removes the information about the old primaries on the secondary server (Server A). Also removes the jobs if they exist.
  2. Execute sp_delete_log_shipping_secondary_database. This removes the information about the secondary database. NOTE: This does not remove the DB itself, even though the documentation wording makes it sound like it might. Also, this should be called as part of step 1's stored proc, however, I've found that with orphaned log shipping configs, sometimes it doesn't execute so I run it manually afterwards.
Improve this answer
3
  • Ah, thank you. And YES - the documentation makes step 2 sound like it'll delete the database. I read earlier today and was noping pretty hard.
    – Chris K
    Commented Jan 16, 2020 at 22:41
  • oh boy. After step 1, "Msg 32025, Level 16, State 3, Procedure sp_delete_log_shipping_secondary_primary, Line 51 [Batch Start Line 0] Primary Server SI2DB1, Database PTP_PROD has active log shipping secondary database(s) on the secondary. Drop the secondary database(s) first." Yes - after the failover we renamed the secondary server to what the primary was named. I feel like that's going to be a problem here.
    – Chris K
    Commented Jan 17, 2020 at 18:02
  • Flipped the order of the steps and seems good now.
    – Chris K
    Commented Jan 17, 2020 at 18:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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