What is a transaction log?

A transaction log is a file – integral part of every SQL Server database. It contains log records produced during the logging process in a SQL Server database. The transaction log is the most important component of a SQL Server database when it comes to the disaster recovery – however, it must be uncorrupted. After each database modification – transaction occurrence, a log record is written to the transaction log. All the changes are written sequentially

What does a SQL Server transaction log store?

A transaction log stores every transaction made to a SQL Server database, except some which are minimally logged like BULK IMPORT or SELECT INTO. Internally it is split into the smaller parts called Virtual Log Files (VLFs). When one VLF becomes full logging continue to write into the next available in the transaction log. The transaction log file can be represented as a circular file. When the logging reaches the end of the file it starts again from the beginning, but only if all the requirements has been met and the inactive parts has been truncated. The truncation process is necessary to mark all inactive parts so they can be used again and overwritten

A log record is no longer needed in the transaction log if all of the following are true:

• The transaction of which it is part has committed
• The database pages it changed have all been written to disk by a checkpoint
• The log record is not needed for a backup (full, differential, or log)
• The log record is not needed for any feature that reads the log (such as database mirroring or replication) [1]

Logical log is an active part of the transaction log. A Log Sequence Number (LSN) identifies every transaction in the transaction log. The MinLSN is the starting point of the oldest active transaction in the online transaction log

Can SQL Server database work without a transaction log?

No, that is not possible due to the SQL Server design and ACID compliance. ACID stands for atomicity, consistency, isolation, and durability. All the transactions must fullfil these characteristics:

• An atomic transaction is either fully completed, or is not begun at all
• A transaction enforces consistency in the system state by ensuring that at the end of any transaction the system is in a valid state
• When a transaction runs in isolation, it appears to be the only action that the system is carrying out at one time
• A transaction is durable meaning that once it has been successfully completed, all of the changes it made to the system are permanent [2]

Can one SQL Server database have more than one transaction log?

Yes, that is possible, but it’s only recommended in the specific situations. Adding multiple transaction log files will not improve performance of the SQL Server database in any way. Writing can occur only into one file at the time, so parallel I/O operations are not possible

Having multiple transaction log files is recommended only if the first one is full or the disk drive is running out of space. Either way, these problems should be addressed earlier and handled by creating transaction log backups and monitoring available space on the disk drive

Why is the SQL Server transaction log growing?

Every transaction is followed by logging into the online transaction log. During SQL Server work, the transaction log grows if changes are made into a database, therefore maintaining the transaction log is crucial for proper SQL Server operation

There are three recovery models available in SQL Server, depending on which one is used transaction log growth is manifested differently:

• Simple recovery model – the transaction log backups are not supported. The truncation process is automated and space is reclaimed for reuse. There is data loss risk because changes since the most recent database backup are exposed. In the Simple recovery there is little chance for the transaction log growing – just in specific situations when there is a long running transaction or transaction that creates many changes
• Bulk-logged recovery model – the transaction log backups are supported and needed on regular basis. There is no automated process of transaction log truncation, the transaction log backups must be made regularly to mark unused space available for overwriting. Bulk-logged recovery model reduces transaction log space usage by using minimal logging for most bulk operations
• Full recovery model – the transaction log backups are supported and needed on regular basis. There is no data loss risk under the normal circumstances. There is no automated process of transaction log truncation, the transaction log backups must be made regularly to mark unused space available for overwriting. In the Full recovery there is biggest chance of the transaction log growing because all transactions are logged

How to maintain a transaction log in SQL Server?

A transaction log maintenance is important task in SQL Server administration. Monitoring is recommended on daily basis or even more often is a SQL Server database has high amount of traffic. The transaction log space can be monitored by using the DBCC SQLPREF command:

• Database Name – Name of the database for the log statistics displayed
• Log Size (MB) – Current size allocated to the log. This value is always smaller than the amount originally allocated for log space because the Database Engine reserves a small amount of disk space for internal header information
• Log Space Used (%) – Percentage of the log file currently occupied with transaction log information
• Status – Status of the log file. Always 0 [3]

The transaction log should be backed up on the regular basis to avoid the auto growth operation and filling up a transaction log file. Space in a transaction log can be truncated (cleared) through SQL Server Management Studio by selecting Transaction Log as backup type or through CLI by executing the following command:

That backed up space can be reused again and it’ll be overwritten with new transactions. Some operations can’t be combined and must be executed separately:

Do I need SQL Server transaction log backups?

Yes, that is one of the most important resource when it comes to disaster recovery. They are not needed (and available) only if the Simple recovery model is used – but there is data loss exposure. Majority of Database Administrators uses 15 minutes interval or even less for a high traffic SQL Server databases. The transaction log backups are important because when taken they mark inactive VLFs that can be used for writing down new transactions