As part of our DR solution, we have attempted to enable log shipping for a heavy transaction load database. While the configuration completes successfully, the first transaction log backup job to kick off after the completion of the log shipping configuration runs continuously and grows exponentially in size. On one occasion, that first transaction log backup job ran for 12 hours with a file size 3X greater than the 27 GB full backup file for the database. We killed that process. Recently, we tried a twist on the approach using a differential as explained below, but the transaction log backup job still ran with an ever growing file size.
This process was run during weekend low use hours
7:46 am – log shipping configuration kicks off
9:32 am – backup file is stored in network share folder. File size is 26.1 GB
9:30 pm – Log shipping configuration completes. – I disable the log shipping backup, copy, and restore jobs
9:31 pm – I enter command to backup database with differential
9:33 pm – Differential completes with a file size of 768 MB. – I re-enable the backup and copy jobs to get that process moving along after the differential – I copy the differential file to the secondary location
9:45 pm – The first transaction log backup job kicks off
9:59 pm – After the Differential file is copied, I restore the database on Secondary using the differential
11:02 pm – The restore of the differential is still running – The transaction log backup job that was created at 9:45 am is still running with a file size of 28 GB and still growing.
We ultimately killed this process due to space issues as the transaction log backup job never completed.
Has anyone experienced this scenario before? Is there anything we could change to improve the process time on the transaction log backup job? Given the heavy transaction load, I wonder if it would be best to implement an alternative DR solution for this particular database.
I know this may be old,but adding some pointers which will help you.
1.When database is set to Bulklogged recovery model,Tlog will contain copy of data files too,so your Tlogs size will be big
2.further you might want to check what is happening during backups and restores using below trace flags.
dbcc traceon(3004,3605,-1)
3.Same trace flag can be applied to restore as well
4.further if restore is taking some much time,this might be due to huge transactions which are rolledback.See below link for more details
http://www.sqlskills.com/blogs/paul/why-could-restoring-a-log-shipping-log-backup-be-slow/
5.You also can enable Instant file initilization to speedup restores as this will help in growing data files instantly
you also can check if there is network latency by using perfmon counters.