SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reducing Log Shipping Restore Latency


Reducing Log Shipping Restore Latency

Author
Message
Maddave
Maddave
Right there with Babe
Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)

Group: General Forum Members
Points: 732 Visits: 1466
Hi,

We use log shipping in our 2008 R2 databases. On one of our large heavily used databases, transaction log backups are run every two minutes. Each TL backup is around 50MB in size when the primary server is used heavily. We use TDE on the database and backup compression.

The files are transferred quickly, but the restore job takes a long time during peak times. For example when heavy load is placed on the server, I've noticed the restore job to fall behind by nearly 24 hours. The transaction logs are safely copied to the remote server, so in the event of primary failure, we would be able to restore back, up to the last copied file, but the restore time objective could potentially be 24 hours whilst we wait for the restores to complete.

Can anyone help with why the restores are taking so long? What factors do I need to consider? The DR server is slightly different spec to production so I am expecting it to be hardware related, but would be good to get other ideas.
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20138 Visits: 17244
Is the database in restore mode or Standby\readonly?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Maddave
Maddave
Right there with Babe
Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)

Group: General Forum Members
Points: 732 Visits: 1466
Log shipping for the database was configured in Standby/readonly mode, but the DB is in restore mode when it is restoring the transaction logs. When it falls behind, because it is restoring all the files in turn, it stays in restoring mode for the duration, until the load reduces and it catches up with it's self. Then it goes back to standby between the normal restore job schedule.
Maddave
Maddave
Right there with Babe
Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)

Group: General Forum Members
Points: 732 Visits: 1466
As a side note. Logs are taking about 20 minutes to restore for a 50 MB file. The number of changes is around 4800 inserts during the two minute period between log backups.

Looking at the failover server, CPU is minimal, but SQL Server has hit its MAX memory setting and this seems to be the bottleneck, although not sure why.
Ian Scarlett
Ian Scarlett
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2235 Visits: 6887
Restoring with standby can place a LARGE overhead on the restore of each transaction log backup file.

Leaving the database in standby means that SQL Server has to leave the database transactionally consistent (i.e. no half done transactions). This means undoing any changes made by uncommitted transactions, and writing them away so it can redo them immediately prior to restoring the next transaction log backup.

As you are backing up the logs every 2 minutes, you will have a bigger chance of having uncommitted transactions spanning log backups, therefore causing a lot of redo work before restoring the next log backup and a lot of undo work after restoring.

Are you actually using the database for any read-only work? I'm guessing not, given the frequency of backups, as users would have to be kicked off before each backup is restored.



Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20138 Visits: 17244
Do you absolutely require the secondary database to be restored that regularly, have you thought of deferring the copy and\or the restore jobs to run at low usage periods. The rate you are at the moment i'd be surprised if anyone were able to query the database as they'll constantly be disconnected.

Please post the results of the following query against the secondary server


exec sys.sp_help_log_shipping_secondary_database 'yoursecondarydb'



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Maddave
Maddave
Right there with Babe
Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)Right there with Babe (732 reputation)

Group: General Forum Members
Points: 732 Visits: 1466
Ah! That makes perfect sense when you think about the redo overhead! I am not sure why we have the DB in standby mode. We do not report on it and you're right, with a restore period so small, it is pointless.

I have changed the database to be in restore mode and would you believe it, each backup file is taking about 40 seconds to restore now! Unbelievable!! Talk about an overhead. From 10 to 20 minutes a file down to less than a minute!

Thanks all for your help. Definitely learnt something today!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search