Data Management

Understanding the importance of transaction logs in SQL Server

SQL Server transaction log maintenance

By Mark Strawmyer

Transaction logs are a vital yet often overlooked component of database architecture. They are often forgotten because they are not something actively maintained like the schema contained within a database. In this article we’ll examine how transaction logs are used in Microsoft SQL Server, maintenance and potential problems with them, how they can be used to restore a database, and finally, optimizing them for performance.

SQL Server transaction logs
A transaction log is a sequential record of all changes made to the database while the actual data is contained in a separate file. The transaction log contains enough information to undo all changes made to the data file as part of any individual transaction. The log records the start of a transaction, all the changes considered to be a part of it, and then the final commit or rollback of the transaction. Each database has at least one physical transaction log and one data file that is exclusive to the database for which it was created.

SQL Server keeps a buffer of all of the changes to data for performance reasons. It writes items to the transaction log immediately, but does not write changes to the data file immediately. A checkpoint is written to the transaction log to indicate that a particular transaction has been completely written from the buffer to the data file. When SQL Server is restarted, it looks for the most recent checkpoint in the transaction log and rolls forward all transactions that have occurred from that point forward since it is not guaranteed to have been written to the data file until a checkpoint is entered in the transaction log. This prevents transactions from being lost that were in the buffer but not yet written to the data file.

Additional Resources

SQL Server Consolidation
In this presentation, Brian Knight discusses different approaches to managing an enterprise SQL Server environment and reducing costs with SQL Server consolidation.

SQL Server Date and Time
Over the last several months there have been several questions in the forum related to SQL Server Date and Time.  The information in this Database Journal article may prove helpful to those that might be struggling with the concept:

An update from Ramon Padillia

Transaction log maintenance
Transaction logs can present problems because they are often forgotten about until an issue occurs. The log continues to grow as operations are performed within the database. While the log continues to grow, the available disk space decreases. Unless routine action is taken to prevent it, the transaction log will eventually consume all available space allocated to it. If the log is configured to grow indefinitely as is the default, it will grow to consume all available physical disk space where it is stored. Either scenario causes the database to stop functioning.

Regular backups of the transaction log will help prevent it from consuming all of the disk space. The backup process truncates old log records no longer needed for recovery. The truncation process involves marking old records as inactive so they can be overwritten, which prevents the transaction log from growing too large. If frequent backups are not made, then the database should be configured with the “simple recovery model” option. The simple recovery model will force the transaction log to be truncated automatically each time a checkpoint is processed.

The truncation process that occurs as a result of a backup or the checkpoint will mark old log records as inactive so they can be overwritten, but it does not reduce the actual disk space allocated to the transaction log. The logs will keep the space allocated even if it is not used. This is where shrinking comes into the maintenance picture. The act of shrinking the log removes the inactive records and reduces the physical size of the log file.

A log is shrunk when a DBCC SHRINKDATABASE statement is executed against the owning database, a DBCC SHRINKFILE is executed against the specific transaction log, or an autoshrink operation occurs if it is enabled on the database. When shrinking the log, it is first truncated to mark inactive records and then the inactive records are removed. Depending upon how you are trying to shrink the log, you may or may not see immediate results. Ideally, shrinking should be performed on a scheduled basis so that it is forced to occur at points when the database utilization is lower.

Restoring a database
Transaction log backups can be used to restore a database to a specific point in time. A transaction log backup alone is not sufficient to restore a database. A backup of the data file is required as well. The data file backups are applied first. A full data file backup is restored and followed by any differential backups of the data file. It is very important when restoring the data file backup not to mark the recovery as complete, otherwise no transaction log backups can be restored. Once the data file restore is complete, the backups of the transaction logs are applied to return the database to its desired state. If there have been multiple transaction log backups since the last database backup, then the transaction log backups need to be restored in the order in which they were created.

There is another process known as log shipping that can be used to keep a hot stand-by of a database available. When log shipping is configured, a full backup of the database is copied to another server. From that point forward, transaction logs are periodically sent to the other server where they are automatically restored in the stand-by database. This keeps a hot spare of the server available. It is also commonly used to keep a reporting server up to date with recent data changes. Another server, known as a monitor server, can be configured to watch the shipping to make sure that it occurs on the scheduled interval. If it does not occur for some reason, then the monitor server will record an event to the event log. This makes log shipping a popular choice to be included as a part of disaster recovery planning.

Optimizing for performance
Transaction logs play a vital role in the function of a database. As a result, they can have a direct impact on the overall system performance. There are certain configurations that can be made that will optimize the performance of transaction logs. The transaction log is a sequential write to the physical disk, and there are no reads that occur as a part of the logging process. Thus, if the logs are isolated on a separate disk, it will optimize the performance because there will be nothing interfering with the writing of the transaction log.

Another optimization relates to the growth of the transaction log size. The log can be configured to grow as a percentage of the total size or at a set physical rate. Regardless of the growth option, the size of the growth should be large enough to prevent the log from needing to continually expand. If the growth rate is set to low the log may be forced to continually expand, which will slow the database performance.

Editor's Picks