September 18, 2006 at 12:12 pm
We have a maintenance plan set up to backup the database then the transaction logs, etc...
We have had issues where the backup of the database fails and then the rest of the maintenance plan does not complete and therefore our tansaction logs do not get backed up among other tasks.
My question is. If I set up the tran log backup on a seperate maintenance plan am I going to run into issues? Is the tran log going to backup while the database backs up or is it going to wait? If it does backup the tran log while the db is backing up does that cause me problems? I mean it would then technically clear the active transactions but would have a completion date earlier than the completion date of the db backup. Will I then have issues if I try to restore the db backup and then load that tran log?
Thanks very much for your assistance.
September 18, 2006 at 11:27 pm
no response? perhaps i'm not being clear. surely someone must know this.
the real question is, if i set up my full database backup in one maintenance plan and my tran log backup in another maintenance plan, and they try to run at the same time will there be a problem?
from my research on the web i am understanding that when the backup starts it forces a checkpoint, records the last lsn in the log before starting and then does not allow the log to truncate while backup is in operation, then records the last lsn in the log once finished so it has a range of lsn's that occurred during the backup. in this way the data remains consistent and that portion of that log can then be used during recovery.
if this info is correct that means my tran log backups will wait until database backup is done? this sounds correct but risky also. if this is true could i not lose data if a catastrophic crash happens hours into a database backup?
the reason for my query is because we have some very large databases that are taking 8+ hours to backup. sometimes the backup fails. if the backup fails then the maintenance plan does not complete either. with the tran log backup in the same maintenance plan the tran logs do not get truncated and grow unabated until we get disk space messages from our monitoring system.
i have read muliple ms articles on tran logs, db io, backup, restore. as i stated above i think i have a grasp on it but believe i am missing something. any assistance you can provide (even if it's to point me to a resource) would be much appreciated.
September 19, 2006 at 6:49 am
I use different maint plans for backups & trans logs without a problem. If the log backup tries to run while the full backup is running, it will fail, but not a big deal. It will run again at it's next scheduled time. But since you should know how long the full backup takes, you should be able to schedule them so they don't conflict.
However, 1 maint plan for db backups & log backups creates 2 separate jobs with their own schedules, so I don't think separate Maint plans will make any difference. Why do the backups fail ? 8 hour backups !?! How big are they ?? Buy one of the backup products that will cut your backup time by 60 - 80 percent and save 60-80 percent disk space too (if you're backing up to disk).
If your disks are filling up with trans logs, then make 2 different trans log backups and point 1 to drive F and 1 to drive G to spread them out.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply