administrator

A SQL Server database has a transaction log that records all transactions and the database modifications made by each transaction. We recommend you perform transaction log backups frequently, so you do not lose any valuable data in the event of a failure on the production server. If a disaster does occur on the production server, it is vital that the most recent database status can be restored. To restore data successfully, you must use online or differential database backups plus a series of transaction log backups that correlate as closely as possible to the time of the disaster. For this purpose, it is critical to perform transaction log backups on a regular basis.

Log truncation frees space in the transaction log file for reuse by the transaction log. You should truncate the transaction log regularly to reduce the possibility of the transaction log getting full. 

Keep in mind that utilizing transaction logs adds additional strain to the SQL server and must be balanced properly for the available hardware resources.

For details about setting up and using SQL Server transaction logs, see the Microsoft SQL Server documentation.

Backup frequency

It is important to perform transaction log backups often enough to reduce the risk of losing many committed business transactions in case of a disaster! For example, if you only perform a transaction log backup every two hours, in the case of a disaster up to two hours of committed business transactions would not be able to be restored.

Frequent transaction log backups reduce the risk of losing business transactions. In many productive customer scenarios, performing a transaction log backup every 10-30 minutes is an acceptable frequency. However, in combination with SQL Server log shipping, you can create SQL Server transaction log backups even every 2-10 minutes.