In this section, we will talk about what exactly are transaction logs in an SQL Server database. What purpose do they solve and how they come in to play in case of disaster recovery plan.
What is a Transaction Log?
It’s basically a file. These files are extremely important for every server database. The file contains all the records that are used to log the activities in the database. It happens to be the most crucial part of servers if we talk about their usage during SQL server recovery after a disaster has happened. However, in order for this file to function, it has to remain uncorrupted. Every time a database modification takes place or some transaction happens, a record gets created in the transaction log. The record is created sequentially.
What is Stored in the Transaction Log?
Transaction log contain information related to all the transactions that take place within an SQL server database. Few exceptions are there which are logged minimally such as SELECT INTO or BULK IMPORT. The logs are internally divided into VLFs (Virtual Log Files). When these log files become full, next available transaction log continue to store the logging information. When a file reaches its end of logging, the file starts logging from the beginning.
If a log record meets following requirements, it no longer needs to be stored in the transaction logs:
- The transaction is committed
- The record is not required for backup (log, differential, or full)
- The record is not important for any feature that involves reading the log. For example, database replication or mirroring.
- The database pages changed by the log have been written on to the disk by some checkpoint.
Log Sequence Number also known as LSN is used for identifying all the transactions in the transaction log. Min LSN offers the starting point for old active transaction in the log.
Can Databases Function without Log Files?
ACID (atomicity, consistency, isolation, and durability) compliances and the way SQL Server is designed make it impossible for database to functioning without log files.
Can One Database Contain Multiple Transaction Logs?
Yes, it can but only in some specific situations. Having multiple log files in no way enhances the performance of the database. Logs are recorded into one file at a time. So, log files working parallel is not a possibility.
Multiple log files are required only in situations when the disk is running into low space or if the first log file has become full. So, these things need to be considered beforehand by creating log backups. Also, disk space availability should be monitored from time to time.
Do Server Administrators Need to Back up Transaction Logs?
It’s very important that they do. Log backups are used during disaster recovery procedure. If the simple recovery model is used, then you may not need it, but there is always a possibility of data loss. For SQL server database with high traffic, most administrators would use an interval of fifteen minutes (sometimes even less) before taking the backup.
Peter Song is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including outlook recovery and PDF recovery software products. For more information, visit www.datanumen.com