March 22, 2009 at 5:44 pm
If you schedule a transaction log backup every 15 mins, and do a nightly full backup at 9pm, what will happen to the transaction log backup job running every 15 mins if the nightly full backup job takes 35-40 minutes?
Do you need a maintaince plan clean-up task to remove older transaction log backups or will they automatically be removed upon a full backup being taken?
March 22, 2009 at 6:14 pm
Following on from my first post, See attached screen-print.
I've got the attached maintenance plan with just that task attached to job running every 15 mins.
The problem is it doesn't seem to clear the transaction log, when it should shouldn't it?
As another option, is there a reason why you wouldn't/couldn't/shouldn't run a Differenital backup every 15 mins? WOuldn't that make it easy to restore given disaster as you'd just need to restore the full backup and the latest differential backup rather than a series of transaction log backups?
March 22, 2009 at 6:43 pm
JamesNZ (3/22/2009)
If you schedule a transaction log backup every 15 mins, and do a nightly full backup at 9pm, what will happen to the transaction log backup job running every 15 mins if the nightly full backup job takes 35-40 minutes?Do you need a maintaince plan clean-up task to remove older transaction log backups or will they automatically be removed upon a full backup being taken?
The transaction log backup will run with no problems. There use to be a problem in SQL Server 2000 where they would block, but that issue has been corrected in 2005 and above.
And yes, you are going to need a clean-up task to remove older backup files and transaction log backup files. I would recommend adding that cleanup task following your cleanup task that cleans up the backup files (and set it to an increment that is slightly larger - for example, if you are removing backup files older than 2 days, remove transaction log files that are older than 49 hours).
The problem is it doesn't seem to clear the transaction log, when it should shouldn't it?
Not sure what you mean here - backing up the transaction log marks the space as reusable.
As another option, is there a reason why you wouldn't/couldn't/shouldn't run a Differenital backup every 15 mins? WOuldn't that make it easy to restore given disaster as you'd just need to restore the full backup and the latest differential backup rather than a series of transaction log backups?
Yes, you could do this - but remember that differential backups backup all changes since the last full backup. Transaction log backups backup all changes since the last transaction log backup. Each differential backup would get larger and larger whereas the transaction log backups should all be around the same size.
To reduce the amount of time needed to restore your system, you could do something like:
Daily Full Backup
Differential every four hours
Transaction Log backups every 15 minutes.
With the above, worse case to restore your system would be:
Restore Full Backup
Restore Latest Differental
Restore Transaction Logs since latest differential (at most, this will be 16 files).
As opposed to:
Daily Full Backup
Transaction Log Backups every 15 minutes
Restoring would be (worse case)
Restore Full Backup
Restore 96 transaction log backups (4 per hour, 24 hours - 96 files, or 97 if you were able to get a tail log backup).
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
March 22, 2009 at 8:42 pm
Thanks for the great response Jeffrey 🙂
The transaction log backup will run with no problems. There use to be a problem in SQL Server 2000 where they would block, but that issue has been corrected in 2005 and above.
Thanks, that's good to know.
And yes, you are going to need a clean-up task to remove older backup files and transaction log backup files. I would recommend adding that cleanup task following your cleanup task that cleans up the backup files (and set it to an increment that is slightly larger - for example, if you are removing backup files older than 2 days, remove transaction log files that are older than 49 hours).
Currently only 1 days backups are kept on the hard-disks, they're written out to tape each night and then deleted the following night when the full backup job runs.
So the process in doing a full backup at 9pm each night would be:
Full Backup
Cleanup *.bak from past 24 hrs
Cleanup *.trn from past 24 hrs
Not sure what you mean here - backing up the transaction log marks the space as reusable.
Mark it as reusable or clean the space completely? A log file for a db didn't shrink down to 0KB, and stayed at 3.2GB, any idea on why that would happen?
Yes, you could do this - but remember that differential backups backup all changes since the last full backup. Transaction log backups backup all changes since the last transaction log backup. Each differential backup would get larger and larger whereas the transaction log backups should all be around the same size.
To reduce the amount of time needed to restore your system, you could do something like:
Daily Full Backup
Differential every four hours
Transaction Log backups every 15 minutes.
With the above, worse case to restore your system would be:
Restore Full Backup
Restore Latest Differental
Restore Transaction Logs since latest differential (at most, this will be 16 files).
As opposed to:
Daily Full Backup
Transaction Log Backups every 15 minutes
Restoring would be (worse case)
Restore Full Backup
Restore 96 transaction log backups (4 per hour, 24 hours - 96 files, or 97 if you were able to get a tail log backup).
Thanks for that.
If disk-space isn't as big an issue, and 1 hour data-loss is acceptable, is it worth simply just running a differential backup every hour and leaving it at that?
March 23, 2009 at 1:41 am
JamesNZ (3/22/2009)
Mark it as reusable or clean the space completely? A log file for a db didn't shrink down to 0KB, and stayed at 3.2GB, any idea on why that would happen?
Mark as reusable. Log backups do not shrink the log.
While a full backup is running, the log backups cannot truncate the log (remove old log records and mark the space as reusable) as the full backup needs the log entries to ensure a consistent restore. The next log backup after the full has finished will truncate the log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 23, 2009 at 2:59 am
GilaMonster (3/23/2009)
JamesNZ (3/22/2009)
Mark it as reusable or clean the space completely? A log file for a db didn't shrink down to 0KB, and stayed at 3.2GB, any idea on why that would happen?Mark as reusable. Log backups do not shrink the log.
While a full backup is running, the log backups cannot truncate the log (remove old log records and mark the space as reusable) as the full backup needs the log entries to ensure a consistent restore. The next log backup after the full has finished will truncate the log.
Thanks for the explanation Gila - will keep an eye on that 🙂
March 23, 2009 at 9:51 am
If disk-space isn't as big an issue, and 1 hour data-loss is acceptable, is it worth simply just running a differential backup every hour and leaving it at that?
We really cannot answer that for you. Is it possible - yes. Would it work - yes. Is it a valid plan - again, yes.
Just note - that if you do this, then you cannot leave your databases in full recovery model. If your databases are in full recovery model you must run transaction log backups. If you don't, you will be posting back here asking why your transaction log is 300GB and keeps growing 😉
If disk space is not an issue - and your biggest concern is how long it would take to recover the system, then yes - I would recommend daily full backups, hourly differentials, and transaction log backups every 15 minutes. Worse case recovery is a restore of 6 files (latest full, latest diff and four transaction log backups).
Just make sure you don't remove old files until you are sure they have been offloaded to other media (generally tape, but could be any other media).
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
March 23, 2009 at 2:59 pm
Jeffrey Williams (3/23/2009)
If disk-space isn't as big an issue, and 1 hour data-loss is acceptable, is it worth simply just running a differential backup every hour and leaving it at that?
We really cannot answer that for you. Is it possible - yes. Would it work - yes. Is it a valid plan - again, yes.
Just note - that if you do this, then you cannot leave your databases in full recovery model. If your databases are in full recovery model you must run transaction log backups. If you don't, you will be posting back here asking why your transaction log is 300GB and keeps growing 😉
If disk space is not an issue - and your biggest concern is how long it would take to recover the system, then yes - I would recommend daily full backups, hourly differentials, and transaction log backups every 15 minutes. Worse case recovery is a restore of 6 files (latest full, latest diff and four transaction log backups).
Just make sure you don't remove old files until you are sure they have been offloaded to other media (generally tape, but could be any other media).
Thanks Jeffrey.
Regarding off-loading to tape. If off-loading to tape is done once a day at 11pm, is there any point in off-loading the differential and transaction log backups to tape, because the full backup would have run at 9pm?
These systems are only used within business hours, so outside of 8am to 7pm, data-loss isn't that important would be acceptable to roll-back to the 9pm full backup should something drastic happen at 5am in the morning for example.
March 23, 2009 at 3:09 pm
That really depends upon your business, but what would happen if your boss came to you and said that you need to restore the system back to yesterday at 8:50pm, right before your full backup - that is offsite, but your differentials and transaction log backups from yesterday are not, and you just got done deleting the files that were online?
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
March 23, 2009 at 3:22 pm
Jeffrey Williams (3/23/2009)
That really depends upon your business, but what would happen if your boss came to you and said that you need to restore the system back to yesterday at 8:50pm, right before your full backup - that is offsite, but your differentials and transaction log backups from yesterday are not, and you just got done deleting the files that were online?
Very good point. Just to check my thinking, if I'm taking a full backup every night at 9pm, I can delete all differential and transaction log backups that are older than 24 hours right and be ok?
Do you just do within the Maintenance Plan Wizard the following options:
* Back Up Database (Differential)
* Back Up Database (Transaction Log)
or do you use any other maintenance tasks as well when you run these?
Currently for the 9pm backup I do the following:
1. Check Database Integrity
2. Backup up Database (Full)
3. Maintenance Cleanup Task that removes all *.bak files older than 23 hrs
Coming back to Differential and Transaction Log backups, do you just to "user databases" I presume and I take it you should do "Verify backup integrity too"?
March 23, 2009 at 3:32 pm
JamesNZ (3/23/2009)[/bVery good point. Just to check my thinking, if I'm taking a full backup every night at 9pm, I can delete all differential and transaction log backups that are older than 24 hours right and be ok?
Do you just do within the Maintenance Plan Wizard the following options:
* Back Up Database (Differential)
* Back Up Database (Transaction Log)
or do you use any other maintenance tasks as well when you run these?
Currently for the 9pm backup I do the following:
1. Check Database Integrity
2. Backup up Database (Full)
3. Maintenance Cleanup Task that removes all *.bak files older than 23 hrs
Coming back to Differential and Transaction Log backups, do you just to "user databases" I presume and I take it you should do "Verify backup integrity too"?
I would have a sub-plan (or separate maintenance plan) for each type of backup. So, we would have a sub-plan for daily backups that have the above steps (although I would delete files older than 25 hours instead). Then, we would have a separate sub-plan for differential backups scheduled every XX hours, and another sub-plan for transaction log backups scheduled every XX minutes.
I would add another task to the daily backup, which would be a step to remove old transaction log backups older than 25 hours to match what I have for backups. The differential backups would be cleaned up with the same step that removes old backup files because both use the same extension (.bak).
I would then make sure that sometime after the daily process has completed - the backup folders are copied to tape (or other media). If I had enough disk space available, I would keep more than a single days backups online, but we don't have that kind of space available.
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
March 23, 2009 at 3:43 pm
Jeffrey Williams (3/23/2009)
JamesNZ (3/23/2009)[/bVery good point. Just to check my thinking, if I'm taking a full backup every night at 9pm, I can delete all differential and transaction log backups that are older than 24 hours right and be ok?
Do you just do within the Maintenance Plan Wizard the following options:
* Back Up Database (Differential)
* Back Up Database (Transaction Log)
or do you use any other maintenance tasks as well when you run these?
Currently for the 9pm backup I do the following:
1. Check Database Integrity
2. Backup up Database (Full)
3. Maintenance Cleanup Task that removes all *.bak files older than 23 hrs
Coming back to Differential and Transaction Log backups, do you just to "user databases" I presume and I take it you should do "Verify backup integrity too"?
I would have a sub-plan (or separate maintenance plan) for each type of backup. So, we would have a sub-plan for daily backups that have the above steps (although I would delete files older than 25 hours instead). Then, we would have a separate sub-plan for differential backups scheduled every XX hours, and another sub-plan for transaction log backups scheduled every XX minutes.
I would add another task to the daily backup, which would be a step to remove old transaction log backups older than 25 hours to match what I have for backups. The differential backups would be cleaned up with the same step that removes old backup files because both use the same extension (.bak).
I would then make sure that sometime after the daily process has completed - the backup folders are copied to tape (or other media). If I had enough disk space available, I would keep more than a single days backups online, but we don't have that kind of space available.
Thanks for that explanation and thanks again for your help 🙂
Am I right with my comment in my previous post around this?
1)
Do you just do within the Maintenance Plan Wizard the following options:
* Back Up Database (Differential)
* Back Up Database (Transaction Log)
or do you use any other maintenance tasks as well when you run these?
2)
Coming back to Differential and Transaction Log backups, do you just to "user databases" I presume and I take it you should do "Verify backup integrity too"?
March 23, 2009 at 3:47 pm
Differential backup in their own sub-plan\maintenance plan.
Transaction log backup in their own sub-plan\maintenance plan.
Yes - only user databases. No reason to do anything else with system databases other than daily backups.
If you can afford the time to verify, then yes - verify the backups. Just remember that using that option does not assure that the backups are actually usable. For that, you need to test them by actually restoring (on a test system, of course).
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
March 23, 2009 at 3:51 pm
Thanks heaps 🙂
March 24, 2009 at 4:14 pm
Got it running transaction log backups every 15 minutes, differential every 3 hours, and full backups at 9pm.
One problem though that I had earlier is that the SQL log file isn't shrinking down. It's staying at 50GB. As I'm now doing this, shouldn't it have been shrunk down and be maintained a fairly small size?
The trn files being generated every 15 minutes are only around the 1MB mark too.
Viewing 15 posts - 1 through 15 (of 66 total)
You must be logged in to reply to this topic. Login to reply