January 6, 2010 at 9:28 am
I have two maintenance plans configured.
Maintenance Plan 1
1. Check DB Integrity
2. Full DB Backup
3. Reorganize Index
4. Update Statistics
5. Maintenance Cleanup
6. Clean Up History
Maintenance Plan 2
1. Transaction Log Backup
2. Maintenance Cleanup
I have Plan 1 executing at 12AM daily, and Plan 2 executing at 12AM every hour.
The full DB Backup doesn't end until about 2:15 AM, so should I set the transaction log backup to start at say 3AM instead of 12AM? I want to do daily full backups followed by hourly tlog backups, but I don't think the timing of each task is correctly setup.
January 6, 2010 at 9:49 am
Brian Tabios (1/6/2010)
I have two maintenance plans configured.Maintenance Plan 1
1. Check DB Integrity
2. Full DB Backup
3. Reorganize Index
4. Update Statistics
5. Maintenance Cleanup
6. Clean Up History
The full DB Backup doesn't end until about 2:15 AM, so should I set the transaction log backup to start at say 3AM instead of 12AM? I want to do daily full backups followed by hourly tlog backups, but I don't think the timing of each task is correctly setup.
Timing all depends on when the data is accessed/changed. If the database is not being used during the night then change the log backups to 3AM is a good choice.
Curious...does Maintenance Plan 1 not complete until 2:15 AM or when you view the history the backup portion is not completing until 2:15 AM.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 6, 2010 at 9:58 am
Curious...does Maintenance Plan 1 not complete until 2:15 AM or when you view the history the backup portion is not completing until 2:15 AM.
Actually the latter, the full backup does not complete until 2:15 AM.
So, Check DB Integrity takes 30 minutes and then the next step (full backup) takes about 1.5 hours.
January 6, 2010 at 10:04 am
I would go ahead and change the log backup to 3AM.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 6, 2010 at 11:54 am
Melton (1/6/2010)
I would go ahead and change the log backup to 3AM.
I would not change the timing of your log backups at all - keep running them every hour. Think about how you would recover your system to the point in time of failure if your system crashed after your backup started, but before it ended.
If you stopped backing up your transaction log at 12am - the system crashes at 2:05am, can you recover the system to 2am? Maybe - if you have access to the transaction, can set it up so you can perform a tail log backup. However, if you already have your backup that ran at 2am - you'll only lose 5 minutes of data.
Remember, how often you backup the log is fully dependent upon the business requirements for recovery. If you know that there can never be any changes to the database after hours - you can probably avoid running transaction logs all the time, but then again - taking a few seconds to backup the transaction log will assure you that you can recover and doesn't cost you anything.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 6, 2010 at 12:14 pm
I would not change the timing of your log backups at all - keep running them every hour. Think about how you would recover your system to the point in time of failure if your system crashed after your backup started, but before it ended.
So let's say I backup tlog's every hour and do 12 AM full backups (which take 2 hours to complete). If a crash occurs at 1:10 AM, I would just need to recover the last sucessful 12 AM full, and every tlog backup up until the 1 AM backup? Does that work?
January 6, 2010 at 12:53 pm
Brian Tabios (1/6/2010)
I would not change the timing of your log backups at all - keep running them every hour. Think about how you would recover your system to the point in time of failure if your system crashed after your backup started, but before it ended.
So let's say I backup tlog's every hour and do 12 AM full backups (which take 2 hours to complete). If a crash occurs at 1:10 AM, I would just need to recover the last sucessful 12 AM full, and every tlog backup up until the 1 AM backup? Does that work?
Not necessarily - how long does it take for the actual backup to run? Let's say it takes 30 minutes from start to finish. The backup itself starts at 1:30am and completes by 2am - now, if your system crashes at 1:45am - how are you going to recover?
You are going to restore the latest backup - the one that was done yesterday and apply every transaction log up to the point in time of failure. If your last transaction log backup was done at 12am - that is as far as you can recover and you will lose 1 hour 45 minutes of data.
If your last transaction log backup was done at 1am - you will lose 45 minutes of data.
How much data loss can your company afford? Be realistic with this number - is an hours loss acceptable and recoverable? How long can your company afford to be down while you perform a restore? Again, be reasonable and build your plan to support the business requirements.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply