increase trans log backup frequency...

Question

increase trans log backup frequency...

schep021

Hall of Fame

Points: 3538
More actions
September 7, 2007 at 1:28 pm

#121454

I plan on increasing the trans log backup frequency of our production system from every hour to every 15 minutes. I am also looking into creating an alert that will automatically perform a backup when the log is 85% used.
My thoughts are that increasing the backup frequency will reduce the potential for the amount of lost data as well as decrease the trans log backup duration.
I am thinking about this correctly? Does anyone have any tips or warnings? Our system can be highly transactional, so I will make sure to perform the swith during off peak hours.

Thanks in advance!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

Our application does over 200 million inserts a day, and we use a 15-minute log backup frequency. Your thoughts - better recoverability and quicker backups vs. taking fewer log backups - are the same ones I had when putting the schedule into play.

-Eddie

Eddie Wuerch
MCM: SQL

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

Indeed, that's the way to do it.

Also when adding the alert, make sure you're notified (extra) because it may point you to "unexpected" high volume of activity.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

schep021 Hall of Fame Points: 3538 More actions · Answer 3

Thank you everyone for your feedback. I think I will take the path of sending out a notification that the log file is becoming large and then set up a job to perform the backup during off peak hours.

Have a great day!

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

Alen's written a good article on this ...

Building Alert-based Transaction Log Backups - Part Two