April 11, 2011 at 3:41 am
I have a question regarding backing up the transaction log for a production db. How often does this need to be done? Every day or more or less often? Does the frequency of backup depend on any specific factors?
A related question concerns shrinking the transaction log. Should this be performed at the same frequency as the backup?
Thanks for any help.
April 11, 2011 at 4:09 am
M Joomun (4/11/2011)
I have a question regarding backing up the transaction log for a production db. How often does this need to be done? Every day or more or less often? Does the frequency of backup depend on any specific factors?
I hope your db recovery model is either full or bulk logged. Secondly, frequency depends on your requirement.
It depends on how critical your application is. If you need point in time recovery, then it is better to schedule the t-log backup more regularly, thereby improving your chances of recovery.
A related question concerns shrinking the transaction log. Should this be performed at the same frequency as the backup?
Thanks for any help.
It is better to avoid shrinking as it would lead to fragmentation. My suggestion is, while creating a db, make sure to analyze the size requirement accordingly, so that your db growth would fall within the limits you set initially for the db. It would be tough, but it is better to avoid manual shrinking as it would lead to problems.
M&M
April 11, 2011 at 4:21 am
Our production transaction logs are backed up every 15-20 minutes.
The frequency is determined by your businesses allowable data loss.
Transaction backups once per day means you have the potential to lose 1 day of database activity. Is that ok with your business ?
There are some good articles about transaction logs on this site that you might benefit from.
For instance, search for articles by "GilaMonster"
April 11, 2011 at 4:40 am
Thanks for the replies. You've given me quite a bit of food for thought.
Unfortunately, our storage facilities are very limited and so backing up the transaction log as frequently as you suggest would simply not be possible. It may be that a less frequent backup is the thing and that the potential loss of a day's database activity is something we'll have to live with.
April 11, 2011 at 4:51 am
I think the total size of you t-log backups should be about the same. One huge daily backup, or many small 20 minute backups.
April 11, 2011 at 4:53 am
homebrew01 (4/11/2011)
I think the total size of you t-log backups should be about the same. One huge daily backup, or many small 20 minute backups.
Ah, did not realise that. Thanks.
April 11, 2011 at 5:02 am
Basically, the transaction log contains the database activity that has occured since the previous backup (full, diff or tran log)
Proper explanations & details here:
April 11, 2011 at 8:09 am
homebrew01 (4/11/2011)
Basically, the transaction log contains the database activity that has occured since the previous backup (full, diff or tran log)Proper explanations & details here:
:thumbsup:
April 11, 2011 at 8:20 am
homebrew01 (4/11/2011)
I think the total size of you t-log backups should be about the same. One huge daily backup, or many small 20 minute backups.
A daily log backup will also result in a much, much larger transaction log, as the log file now has to hold an entire day's transaction log entries instead of 15 minutes of log entries.
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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply