October 24, 2018 at 6:11 am
Hello, we have a log shipping environment set up and it is taking too long to refresh our secondary databases each night. I take a full primary backup each night at 6:00PM and transactions log backups every 10 minutes. One secondary loads at 9:00PM, as second one loads at 10:00PM and a third at 12:00AM. What effect would taking another full backup at 4:00AM have on the log shipping process? Would it still load all the transaction logs between 6:00PM and 4:00AM? Just looking for a way to possibly bypass the overnight transaction log. The undo is huge. I have made an adjustment to take transaction log backups every hour overnight and it has helped a bit. Just looking for more gains.
Thank you,
D
October 24, 2018 at 7:56 am
oradbguru - Wednesday, October 24, 2018 6:11 AMHello, we have a log shipping environment set up and it is taking too long to refresh our secondary databases each night. I take a full primary backup each night at 6:00PM and transactions log backups every 10 minutes. One secondary loads at 9:00PM, as second one loads at 10:00PM and a third at 12:00AM. What effect would taking another full backup at 4:00AM have on the log shipping process? Would it still load all the transaction logs between 6:00PM and 4:00AM? Just looking for a way to possibly bypass the overnight transaction log. The undo is huge. I have made an adjustment to take transaction log backups every hour overnight and it has helped a bit. Just looking for more gains.Thank you,
D
Backups of the primary database won't really have an impact on log shipping. All transactions are in the transaction logs. All the transactions are shipped to the secondaries.
Sue
October 24, 2018 at 4:11 pm
What's the relevance of the nightly full backup with regards to Log Shipping, and why do you want to take a second full backup? Are you only applying logs once a day? Also are you applying the full backup each night as well, then applying the tran logs. It sort of sounds like this reading between the lines. Another thing that isn't obvious from what you say is whether you are running tran log backups throughout the day.
It sounds like you are only applying the log backups during a limited window (6:00 p.m. to 4:00 a.m.). Are you doing this because you're using the secondary for reporting? Generally I don't recommend using Log Shipping databases for reporting, as there are better options for reporting databases.
Taking backups, both full and log, should have no noticeable impact on the server, and if you really want to use a Log Shipped Secondary to report off, I would still run regular log backups all day (at least every 15 minutes), let the Copy Job copy these to the secondary, but only apply the logs in your preferred window. The issue may be that if you have configured the log restore to leave the DB in STANDBY mode, then it will normally do this after every log restore. So each restore involves: Take out of Standby, Apply the Standby File details, Restore the next log, Roll Forward & Back, Set to Standby, start again. This may explain why going to hourly backups improves things, as there are fewer actions. To resolve this you need to set up the Log Restores to NOT set the DB to standby mode, and build a special step to reapply the last log with STANDBY. This is a lot more efficient.
Alternately, use something like Replication (or AoHA on Enterprise) to create a reporting database.
Leo
Nothing in life is ever so complicated that with a little effort it can't be made more complicated.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply