Warm Standy DB Breaks When a Snapshot of it is Created

  • Super Cat

    SSCertifiable

    Points: 7316

    I have a db that is log shipped form Server A to Server B.
    I then take a snapshot of the DB on server B.

    The restore job to the DB on Server B then fails after the snapshot of the DB on Server B is created.
    Why and is there a way around it.

    Thanks

  • Lynn Pettis

    SSC Guru

    Points: 442335

    Super Cat - Wednesday, August 29, 2018 8:45 AM

    I have a db that is log shipped form Server A to Server B.
    I then take a snapshot of the DB on server B.

    The restore job to the DB on Server B then fails after the snapshot of the DB on Server B is created.
    Why and is there a way around it.

    Thanks

    And what is the full error message you are receiving when the restore fails?  Right now all we know is "when I do this it breaks."

  • Super Cat

    SSCertifiable

    Points: 7316

    There is no meaningful error in the agent job.
    Maybe I asked the wrong question?
    Can you successfully snapshot a DB that is in warm standby mode in a log shipping setup.
    If so how?
    I have created it with and without disconnecting users.

  • Super Cat

    SSCertifiable

    Points: 7316

    I would like for the Snapshot to  remain in place while the Standby Db is receiving logs.
    It only works if the snapshot is dropped prior to the TLOG Load job runs

  • Lynn Pettis

    SSC Guru

    Points: 442335

    Unfortunately, without knowing the error(s) you are getting it is hard to provide any meaningful advice.  Also, I don't have an environment where I could attempt trying to recreate the issue you are experiencing.

  • SQL_Chris

    SSC Journeyman

    Points: 90

    Read-only standby mode can be great, but you’ve got to close all open connections to the database and put it in a restoring mode in order to restore future tran log backups to it as part of your log shipping solution. Unfortunately creating a database snapshot modifies the database and creates a connection per say and doesn't close until the snapshot is destroyed/removed so your log shipping solution won't be able to update while a snapshot exists.

  • SQL_Chris

    SSC Journeyman

    Points: 90

    Additionally, I do not know of anyway around this using Log Shipping other than destroying all snapshots prior to the restore.  What I can tell you is that if you were using a Database Mirror or Basic Availability groups in SQL Standard 2016 you can create a snapshot of the otherwise unusable Secondary.

  • Sreekanth B

    SSCertifiable

    Points: 6145

    Agree with Chris. 

    Log Shipping + Database Snapshots == Bummer, dude!!

    Refer to this blog post for more info:
    https://blogs.msdn.microsoft.com/reedme/2009/04/24/log-shipping-database-snapshots-bummer-dude/

  • Sreekanth B

    SSCertifiable

    Points: 6145

    Super Cat - Thursday, August 30, 2018 1:21 AM

    There is no meaningful error in the agent job.

    I tested this on my SQL 2014 test machine and yes, it did reported a very meaningful error in the restore job history after I created DB Snapshot.

  • Super Cat

    SSCertifiable

    Points: 7316

    Thanks for your replies,

    I thought it was the case, but always good to ask to see if anyone found a work around.
    I can’t use mirroring as the source DB is not within my jurisdiction. I only have access to the secondary.

    SQL 2012 gave me nothing to work with in terms of errors. But thanks for setting up the SQL 2014 environment.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply