Optimal Time Interval for Scheduling Transaction Log Backup

transaction-log

I have a database which is currently of size 50 MB. The database is bound to grow by 10 to 15 MB per hour and the data is important. So, I wanted to ask what should be the optimal time interval in which a transaction log backup should be scheduled so that in case there is any data loss then it is as minimal as possible?
Thanks in advance.

Best Answer

The main question to ask yourself is how much data can you afford to lose. That will determine how often you need to backup the log. But there is nothing wrong with running transaction log backups very often, even every minute. This accomplishes 2 things: 1. Provides you with the security that you won't lose data; 2. Improves performance - the less data you backup at any one time the lower your I/O overhead. I recently read a blog post stating that, from a performance point of view, log backups taken every minute greatly reduced I/O pressure. The downside of such frequently run backups is restoring them. As Bogdan rightly mentions in the comments, you should write a script to perform the restores. Depending on how far back in time you need to go you may need to restore a large number of transaction log backups. Doing that manually is very time consuming and prone to errors. There are probably third party tools that help with this as well.