Baseline your servers and optimize your applications with Site24x7 SQL monitoring tool.
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.
The transaction log records all transactions made in SQL Server, including committed and uncommitted transactions. It captures operations and transactions, including:
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.
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:
By default, a database uses the full or simple recovery model. You can switch a database to another recovery model at any time.
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):
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.Fig. 1: Query retrieving all rows from the dm_db_log_space_usage view
Next, execute the T-SQL statement below in the Query window of SSMS to see the log storage details:
DBCC SQLPERF(LOGSPACE) function retrieves the Log Size, Space Used, and Status of the SQL Server, as shown in the image below:
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.
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.
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.Fig. 3: Visible backup options while navigating to the Back Up 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.Fig. 4: The Back Up Database window with the Transaction Log Back up type selected
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.Fig. 5: Backup success message
Next, open the Database Properties window to view the last backup details on the General page.Fig. 6: The last backup statistics
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
RESTORE LOG DB_NAME FROM DISK = 'C:\LOG_BACKUP_LOCATION-2' WITH NORECOVERY
RESTORE LOG DB_NAME FROM DISK = 'C:\LOG_BACKUP_LOCATION-3
Here are a few recommended actions when working with the transaction logs in a production-ready environment:
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.Fig. 7: General tab of the Database Properties window showing the transaction log file location
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
SELECT * FROM sys.database_files;
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.
Learn what is connection pooling, its benefits and how to troubleshoot connection pooling issues between .Net and SQL severs.➤
Learn how you can detect & resolve SQL deadlocks in SQL servers. Learn about the different types of SQL server deadlocks, how they happen & how to fix them.➤
Write for Site24x7 is a special writing program that supports writers who create content for Site24x7 “Learn” portal. Get paid for your writing.Apply Now