Warm Standy DB Breaks When a Snapshot of it is Created

Question

Warm Standy DB Breaks When a Snapshot of it is Created

Super Cat

SSCertifiable

Points: 7332
More actions
August 29, 2018 at 8:45 am

#378503

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

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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: 7332 More actions · Answer 2

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: 7332 More actions · Answer 3

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: 442467 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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 More actions · Answer 8

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: 7332 More actions · Answer 9

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.