Managing SQL Server’s transaction log

The transaction log is a critical component of Microsoft’s SQL Server that ensures data consistency and recoverability. You can use it to recover lost data or reverse an action that has produced unwanted results. SQL Server also uses the transaction log to recover incomplete database transactions when restarted into a recovery state to maintain its data’s consistency during a failure.

This article explores how and why the transaction log is a critical component of SQL Server. It reviews how the transaction log works, how to configure log file settings, how to monitor and manage it, along with best practices for backing up and restoring the log.

Using the transaction log in SQL Server

The transaction log records all transactions made in SQL Server, including committed and uncommitted transactions. It captures operations and transactions, including:

• Create, retrieve, update, and delete (CRUD) data
• Insert statements
• Page allocations and deallocations
• Table creations, droppings, or indexing
• The start and end timestamp values for each database transaction

SQL Server log records, including transaction logs, are stored in physical log files. A database could have multiple log files or use a single large file. There must be at least one log file for each database.

Then, SQL Server divides each log file into multiple virtual log files (VLFs) with fixed sizes determined at log creation time based on the log file size and a fixed algorithm. VLFs could range from hundreds to thousands depending on the physical log file’s size.

After that, the Database Engine determines the number of the VLFs and their sizes and writes log records into the files sequentially. The Database Engine identifies each log record by its unique log sequence number (LSN). For each log record added, the LSN value assigned is increased. And the higher the LSN value, the newer the log record.

Configuring the transaction log

Log record storage slows performance, so SQL Server provides configuration options to customize log storage according to your use case. These configurations allow you to optimize the transaction logs’ performance, handle log growth and fragmentation, and prevent data loss under edge-case conditions.

SQL Server provides three recovery models, each affecting the transaction log’s operation and data recoverability. A recovery model is a database property that enables you to control transaction logging, whether the transaction log requires or allows backing up, and the kinds of restore operations available. The recovery models are:

• The simple model — This is the most basic recovery option. It’s meant for when you don’t need log backups and restores. You also use it when you want the SQL Server to manage transaction log storage automatically.
• The full model — Use this model when data recoverability is a concern, as it allows you to perform log backups and support restore operations.
• The bulk-logged model — Like the full model, the bulk-logged model uses minimal logging for bulk operations. The bulk-logged model does not support restoring to a specific point in time during the bulk-logged process but supports point-in-time recovery up to the last log backup before the bulk-logged operation.

By default, a database uses the full or simple recovery model. You can switch a database to another recovery model at any time.

Monitoring the transaction logs

You need to monitor the logs routinely to ensure their size and performance don’t cause problems. To gain insight into your transaction logs and determine which configuration options to use, you can use database console commands (DBCC) with the Transact-SQL (T-SQL) syntax or dynamic management views (DMV). Microsoft’s documentation recommends the DMV statements instead of the DBBC functions for SQL Server 2012 and more recent versions.

The two following code blocks show how to view the transaction log statistics using the DBBC and DMV options.

Replace the DB_NAME placeholder with your database name and execute the T-SQL statement below in the Query window of SQL Server Management Studio (SSMS):

USE DB_NAME; 
GO 
SELECT * FROM sys.dm_db_log_space_usage;

As shown in the image below, the command above accesses the dm_db_log_space_usage property from the SampleDB database.

Next, execute the T-SQL statement below in the Query window of SSMS to see the log storage details:

DBCC SQLPERF(LOGSPACE); 
GO

The DBCC SQLPERF(LOGSPACE) function retrieves the Log Size, Space Used, and Status of the SQL Server, as shown in the image below:

Troubleshooting common transaction log errors

While running SQL Server, you’re bound to encounter errors related to the operation of the transaction logs. This section identifies two causes of common errors — LOG_BACKUP log_reuse_wait and ACTIVE_TRANSACTION log_reuse_wait — to help you spot and resolve them.

LOG_BACKUP log_reuse_wait indicates an error from an attempt to truncate your transaction logs to free up space. Usually, this error occurs when you try to truncate the transaction logs on a full or bulk logged recovery model without performing a backup first. Performing a backup before truncating the logs avoids this error.

The ACTIVE_TRANSACTION log_reuse_wait error indicates that long-running transactions are causing the transaction logs to fill up. Opening a DMV using sys.dm_tran_database_transactions will show the transactions. Then, the database_transaction_begin_time, database_transaction_state, and database_transaction_begin_lsn columns help you spot the long-running transaction.

Using the DBCC OPENTRAN statement, you can trace the source of the long-running transaction and commit it. Although not recommended, your last resort may be to end the long-running transaction to resolve the error abruptly. In this case, the KILL statement does the job.

Performing SQL Server transaction log backups

You should perform regular backups to ensure your data is recoverable after a disaster. It’s important to note that transaction log backups are only possible for the full or bulk-logged recovery models. They aren’t available with the simple model because SQL Server fully manages the transaction logs in that case.

Performing regular transaction log backups is necessary to enable point-in-time recovery of the database to prevent data loss in the event of a system failure and for disaster recovery.

A backup triggers the automatic truncation of the logs to free up the backed-up records. SSMS allows you to create and schedule log backups using the user interface. You can also perform the backups programmatically using T-SQL statements or a PowerShell script.

The demonstration below shows you how to perform a backup using the SSMS interface.

In SSMS, click Database item > Tasks > Backup on the Object Explorer panel to open the Backup window.

Select the Transaction Log option from the Backup type list to back up only the transaction log.

Note: Failure to perform a transaction log backup without a previous full backup will result in an error.

Depending on the size of the database, a backup operation could take a few seconds to minutes or even hours. The Progress spinner in the bottom left of the Backup window indicates the status of the process.

After the process writes the data to a file with a .bak extension, you’ll receive a success message.

Next, open the Database Properties window to view the last backup details on the General page.

Restoring transaction logs

In an outage (or for another reason), you can restore a transaction log to a specific point in time using either the SSMS or the RESTORE LOG T-SQL statement. Note that you must have RESTORE permission and exclusive access to the database for log restoration.

The following code shows the T-SQL syntax to restore a basic transaction log using a backup log file on a hard drive.

RESTORE LOG DB_NAME FROM DISK = 'C:\LOG_BACKUP_LOCATION' WITH NORECOVERY 
GO

RESTORE LOG DB_NAME FROM DISK = 'C:\LOG_BACKUP_LOCATION-2' WITH NORECOVERY 
GO

RESTORE LOG DB_NAME FROM DISK = 'C:\LOG_BACKUP_LOCATION-3 
GO

Best practices for managing transaction logs

Here are a few recommended actions when working with the transaction logs in a production-ready environment:

• Use automation when managing transaction logs — Monitoring and backing up your transaction logs over time adds to your toil. One way to automate the management process is to create scheduled PowerShell Jobs that use the SQL Server module to perform an action. SSMS also provides the Script Action to Job feature for scheduled backups using scripts.
• Use a smart transaction log backup strategy — Beginning with SQL Server 2017, the dm_db_log_stats dynamic management function (DMF) now includes a numeric log_since_last_backup field to indicate how much the logs have grown since your last backup. You can make your backup scripts smart by using the log_since_last_backup field to set a threshold for your logs. When the logs reach a specific size, your scripts can trigger the backup rather than using a time interval which might be inconsistent as your database generate more logs at different times based on use.
• Store your backup in separate storage — Use online object storage services such as Azure Blob Storage or S3-compatible storage to protect against disasters. You can also use multiple log files for a database, which will be filled sequentially by the SQL Server.
• Use autogrow only as a contingency plan — Autogrow feature poses some challenges, including the time increase from performing expensive transactions. The autogrow feature adds the time used to dynamically expand log size to the time taken to complete a transaction, which may lead to a timeout error. You should place the log file on a separate disk in a second file group and specify a fixed MAXSIZE to prevent autogrow from taking all the disk space.

Transaction log filename extensions

It’s essential to have basic knowledge of the physical log files storing your log records, as you might have to move these files during a manual backup or restoration process.

Microsoft SQL Server stores your transaction log records in files with a .ldf filename extension. The default filename for transaction log files in SQL Server includes the database name with an additional _log suffix and a numeric extension indicating the sequence number of the log file.

You can change the default log filename and location based on your needs or even move the log file to a separate disk and specify a new location.

Use the Files page in the Database Properties window in SSMS to view the location of the log files for your database.

Alternatively, you can use T-SQL statements to retrieve the location of the log files. The sys.master_files view displays information for all files in all databases on the server, while the sys.database_files view displays only the files for the active database in the current session.

Execute the SQL statement below to retrieve all columns from the sys.database_file view.

SELECT * FROM sys.database_files;

Conclusion

This article taught you how to configure the SQL Server transaction log, how the log records are stored, how to monitor and manage the transaction log, the importance of performing regular backups, and how to back up and restore the log. It concluded with some best practices and a discussion of the file extension used for transaction log files in SQL Server.

The transaction log records all transactions made in SQL Server, including committed and uncommitted transactions. This makes the transaction log a vital component of every SQL Server database, and understanding it helps manage the database successfully.