May 4, 2010 at 1:51 pm
Hello,
I am looking for advice regarding transaction log backups. Presently our company is running full backup jobs on a daily basis - however we use BackupExec so the transaction logs are not being truncated.
The database in question is fairly busy. I now have a disk available that can be used for transaction log backups. My question is how frequently should we be running transaction log backups?
Thanks in advance for any help.
Brian
May 4, 2010 at 1:56 pm
If this DB is a critical database, then:
Daily Full Backups / 4 hour Differential Backup / 30 min Transaction Log Backup
If the database is very Critical and heavy Transactional then:
Daily Full / 3 Hour Diff / 15 min Transactional.
Maninder
www.dbanation.com
May 4, 2010 at 2:13 pm
Maninder,
Thanks for the quick response! What would be the best first step so that we have a good log chain? Would I need to run a fresh full backup - then follow that with a transaction log backup?
I am also wondering - should each transaction log append to the previous one or should each transaction log backup stand alone?
Brian
May 4, 2010 at 2:20 pm
just Change your current Maintenance Plan to include the Differential backups and Trans Backups and you should be good.
Do not worry the initial size of the Trans Log "might" be huge..
Trans logs should not be appended and if possible should go into their Separate folders (separate folder for .bak and separate for .trn).. keep things clean. in the job notification include yourself to notify you if this job fails.
Maninder
www.dbanation.com
May 4, 2010 at 2:25 pm
Maninder S. (5/4/2010)
If this DB is a critical database, then:Daily Full Backups / 4 hour Differential Backup / 30 min Transaction Log Backup
If the database is very Critical and heavy Transactional then:
Daily Full / 3 Hour Diff / 15 min Transactional.
I don't think I fully agree. Unfortunately, it depends. Criticality is one factor another is transaction volume. A high volume, low criticality database might not even do log dumps. There are a bunch of factors.
Also if the disk is local then a local disk failure can mean that it doesn't really matter when the backups occured, you don't have access.. It is very common to backup servers to a file share on a file server or a mounted LUN from a SAN. Either way it isn't local and can be remapped into another machine easily. Unfortunately for small organizations this is less practical due to cost.
For higher transaction volume databases the more often log dumps the better at 5-20 minute intervals. Lower transaction volume databases can go longer between log dumps. I think an upper limit would be 2 hours. Definitely agree on daily full backups. Differential backups, I'm not entirely sold on, they are kind of like log dumps but not really, they contain ALL changed pages since the differential base, so they grow until the next base. I generally don't use them, but I might be pursueded either way on them.
CEWII
May 4, 2010 at 2:26 pm
We are using BackupExec and currently have the daily full backup going to tape. The transaction log backups will be stored on a seperate disk drive.
With the transaction log backups - would it be ok for each days log backups to append or would you suggest having each of them seperate?
Thanks again,
Brian
May 4, 2010 at 2:30 pm
Interval - it depends. What's the maximum amount of data loss allowed in the case of a disaster? Log backup interval should be lower than that.
Each backup to its own file, if possible with date and time in filename. Makes it easier to see what you have and what you need in the case of a restore.
You need a full or diff backup to start the log chain. If you're not taking diffs currently, no need to start now, take a full backup.
This might be worth reading - Managing Transaction Logs[/url]
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
May 4, 2010 at 2:31 pm
Also Pl do check that your jobs do not OVERLAP.. i.e schedule them at diff times.. e.g : if you do a full backup of all the databases at let say 1AM, then Stop or skip all other job from 12.59AM to 2AM (depending upon how much time the back takes).
Then if you are scheduling a Diff Backup, lets say every 4 hours starting 2AM to 12.59AM, do not overlap your translog backup with diff backups and full backups.. so
if your diff backup are like 2-6-10AM-2PM-4PM then your trans log should be 2.10AM etc... Just do not overlap...
Maninder
www.dbanation.com
May 4, 2010 at 2:41 pm
Maninder S. (5/4/2010)
Also Pl do check that your jobs do not OVERLAP.. i.e schedule them at diff times.. e.g : if you do a full backup of all the databases at let say 1AM, then Stop or skip all other job from 12.59AM to 2AM (depending upon how much time the back takes).Then if you are scheduling a Diff Backup, lets say every 4 hours starting 2AM to 12.59AM, do not overlap your translog backup with diff backups and full backups.. so
if your diff backup are like 2-6-10AM-2PM-4PM then your trans log should be 2.10AM etc... Just do not overlap...
As far as I am aware it is not longer an issue to have overlap in your full backups and your log backups. It was not allowed in SQL 2000 but that was changed in 2005.
http://sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-16-Concurrent-log-and-full-backups.aspx
Regards,
Jason P. Burnett
Senior DBA
May 4, 2010 at 2:43 pm
Maninder S. (5/4/2010)
Also Pl do check that your jobs do not OVERLAP.. i.e schedule them at diff times.. e.g : if you do a full backup of all the databases at let say 1AM, then Stop or skip all other job from 12.59AM to 2AM (depending upon how much time the back takes).
Why do you say that?
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
May 4, 2010 at 4:21 pm
Jason P. Burnett (5/4/2010)
Maninder S. (5/4/2010)
Also Pl do check that your jobs do not OVERLAP.. i.e schedule them at diff times.. e.g : if you do a full backup of all the databases at let say 1AM, then Stop or skip all other job from 12.59AM to 2AM (depending upon how much time the back takes).Then if you are scheduling a Diff Backup, lets say every 4 hours starting 2AM to 12.59AM, do not overlap your translog backup with diff backups and full backups.. so
if your diff backup are like 2-6-10AM-2PM-4PM then your trans log should be 2.10AM etc... Just do not overlap...
As far as I am aware it is not longer an issue to have overlap in your full backups and your log backups. It was not allowed in SQL 2000 but that was changed in 2005.
That is because: As per Pauls Blog:
The clearing of the inactive portion of the log is delayed until the full backup completes. This could cause you to have disk space problems if your log generation rate is huge and you're relying on very frequent backups to manage the log size.
Maninder
www.dbanation.com
May 4, 2010 at 4:24 pm
GilaMonster (5/4/2010)
Maninder S. (5/4/2010)
Also Pl do check that your jobs do not OVERLAP.. i.e schedule them at diff times.. e.g : if you do a full backup of all the databases at let say 1AM, then Stop or skip all other job from 12.59AM to 2AM (depending upon how much time the back takes).Why do you say that?
I just replied above abut teh same and here it is again:
In SQL 2000 there was this issues of maintenance plans overlapping as the translog would wait for the full backup to complete and in return both of the job would take 200% more time to complete than scheduled., but starting with 2005 that has changed but with this effect as per PAULS Article: http://sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-16-Concurrent-log-and-full-backups.aspx
The clearing of the inactive portion of the log is delayed until the full backup completes. This could cause you to have disk space problems if your log generation rate is huge and you're relying on very frequent backups to manage the log size.
Maninder
www.dbanation.com
May 4, 2010 at 6:12 pm
[/quote]
That is because: As per Pauls Blog:
The clearing of the inactive portion of the log is delayed until the full backup completes. This could cause you to have disk space problems if your log generation rate is huge and you're relying on very frequent backups to manage the log size.[/quote]
True however, I am not sure that would cause any more growth in your log than suspending your log backups during the duration of the full backup.
Regards,
Jason P. Burnett
Senior DBA
May 4, 2010 at 7:06 pm
Thanks everyone for all the help!
Brian
May 5, 2010 at 1:40 am
Jason P. Burnett (5/4/2010)
True however, I am not sure that would cause any more growth in your log than suspending your log backups during the duration of the full backup.
Correct.
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply