SQL Server Transaction Log — Part 1

Every SQL Server database contains one or more transaction log files in addition to data files. Log files record all transactions and database modifications made by each of them.

This article focuses on the transaction log and how SQL Server logs data modifications to use the data for the database crash recovery.

Introduction to the SQL Server Transaction Log File

As we remember, each transaction is “all or nothing”. If a part of the transaction fails, the entire transaction fails, and the database state remains unchanged.

SQL Server stores a record of each transaction performed on the database into the log file. If some disaster entails the SQL Server shutdown, it uses a transaction log to recover the database to a consistent state with data integrity.

After the restart, SQL Server starts the crash recovery process. It reads the transaction log file to ensure that all valid data is stored in the data files, and uncommitted transactions are rolled back.

During normal operation, SQL Server uses the transaction log too. The information contained in the file is necessary to identify what SQL Server needs to do when a transaction rolls back due to either an error or a user-specified ROLLBACK statement.

How SQL Server Uses the Transaction Log

The transaction log is a physical file with the extension LDF. SQL Server creates it automatically for any new database along with the primary data file (.MDF) which stores the database objects and the data itself.

Whenever the T-SQL code changes a database object or the data it contains, details of the change are recorded as a log record in the transaction log file. 

The log record contains the information of a specific change made to the database (e.g., insert a single row). Therefore, we’ll have a series of log records to describe the effects of a single transaction fully.

Transaction Log Architecture

Log Sequence Numbers

A log record has a unique, auto-incrementing Log Sequence Number (LSN), that allows us to find this record in the transaction log. LSN describes the data change and contains the following information:

• the operation and affected row
• the old and new versions of the data
• the transaction that performed the modification

LSN is comprised of three numbers:

LSN = <VLF sequence # [4bytes]>:<log block # [4bytes]>:<log record #[2 butes]>

Each data file page has an LSN in its page header that identifies the most recent log record whose change is reflected on the page. This is critical for crash recovery.

When the crash recovery runs, it compares the LSNs of log records for committed or uncommitted transactions with LSNs in data-file pages to determine whether there is any redo or undo to be done on those particular log records.

When you create a database, a good practice is to specify the size of the transaction log. If you do not do this, SQL Server will automatically create the transaction log with the default size.

The default size of the transaction log of a new database is the larger of 0.5MB or 25% of the total size of all data files created in the same CREATE DATABASE statement.

You have to be very careful because new portions of the transaction log are always zero-initialized. If you have the CREATE DATABASE statement without specifying the log file size, and you create, for instance, a database of 1TB, SQL Server will create the 250GB transaction log.

As the log has to be zero-initialized, it does not make use of the instant file initialization. This feature was added in SQL Server 2005 to allow the data files to be created or grown almost instantaneously. 

We can see what is going on when we CREATE DATABASE – the zero-initialization occurs of our log using trace flag 3004 which prints messages about zero-initialization and trace flag 3605 that allows for printing those log messages by the trace flag 3004.

The following demo shows how you can see the log file zeroing happening.

1. Execute the following script to make sure that we do not have a database called DBTest2014

USE master;
GO
 
IF DATABASEPROPERTY(N'DBTest2014', N'Version')>0
  BEGIN
    ALTER DATABASE DBTest2014 SET SINGLE_USER
      WITH ROLLBACK IMMEDIATE;
    DROP DATABASE DBTest2014;
  END
GO

2. Enable trace flags to watch zero-initialization

DBCC TRACEON (3605, 3004, -1);
GO
3. Flush the error log
EXEC sp_cycle_errorlog;
GO

3. Create a database

CREATE DATABASE DBTest2014 ON PRIMARY (
  NAME = N'DBTest2014',
  FILENAME = N'D:\DBTest2014_data.mdf')
LOG ON (
  NAME= N'DBTest2014_log',
  FILENAME= N'D:\DBTest2014_log.ldf',
  SIZE = 10MB,
  FILEGROWTH = 10 MB);
GO

4. Read the error log file

EXEC sys.xp_readerrorlog;
GO

Virtual Log Files

The transaction log is split up internally into a series of chunks called virtual log files (VLFs) to simplify the management.

Whenever a transaction log is created, it gives a certain number of VLFs. Newly-created VLFs are inactive and unused. An active VLF cannot be reused until it is made inactive by log clearing.

However, there is one exception – the first VLF in a new database is always active because any transaction log must have at least one active VLF.

Every log file also has a file header page which takes 8KB at the start of the transaction log file. The file header page stores metadata about the file such as size and auto-growth settings.

The number and size of VLFs in a new portion of the transaction log are determined by SQL Server. It is impossible to configure it.

If the newly added size is:

• < 1MB is irrelevant for discussion
• < 64MB there will be 4 new VLFs (each 1/4 of growth size)
• 64MB to 1GB there will be 8 new VLFs (each 1/8 of growth size)
• > 1GB there will be 16 new VLFs (each 1/16 of growth size)

This applies to the initially created transaction log and for each manual or automatic growth that occurs. When you know the formula of the potential number of VLFs and their potential size, it helps to manage the log. Too few or too many VLFs can cause performance problems with transaction log operations.

VLF Sequence Number

Every VLF has a sequence number to uniquely identify the VLF within the transaction log. The sequence number increases by one every time the log management system activates the next VLF. The chain of the sequence numbers gives the currently active set of VLFs.

The start of the transaction log active portion begins with a VLF that has the lowest sequence number and is still active. Inactive VLFs have sequence numbers, but they are not part of the log active portion.

The active portion of the log has log records that are required for some reason by SQL Server.

When you first create a new database, the VLF sequence numbers won’t start at 1. They start with whatever the highest VLF sequence number is in the model database transaction log, plus 1. It is impossible to run out of VLF sequence numbers. SQL Server has code that will force the instance to shutdown if a VLF sequence number ever wraps around to zero (if the next VLF sequence number is less than the previous one).

VLF and Log Blocks

Within VLFs, there are variably sized log blocks. The minimum size of the log block is 512 bytes and log blocks grow up to a maximum size of 60 KB. The size is set when one of the following cases occurs:

• A transaction generates a log record to commit of finish aborting a transaction
• The log block size reaches 60KB without a transaction committing or aborting

There are log records inside a log block (colored on the diagram). Log records are also variably sized. The diagram shows that log records from multiple concurrent transactions can exist within the same log block. The log records are stored in the order written similarly to a data-page file.

Every VLF contains a VLF header with the following information:

• Whether the VLF is active or not.
• The log sequence number when the VLF was created.
• The current parity bits for all 512-byte blocks in the VLF.

The parity bits start at 64 for the very first time of usage VLF. If VLF becomes inactive, but further reactivated, the parity bits will become 128. These are used during crash recovery.

Examining transaction log details – DBCC LOGINFO

The only way to look at the transaction log structure is to use the undocumented DBCC LOGINFO command. The syntax for the command is:

DBCC LOGINFO [({'dbname | dbid'})]

If you do not specify dbname and dbid, it will dump you the log contents for the current database. 

The result is one row for every VLF that is in the transaction log for that database. The returned fields are:

• RecoveryUnitId — added in SQL Server 2012 but currently unused
• FileId — transaction log file ID within a database.
• FileSize — VLF size in bytes.
• StartOffset — starting offset of the VLF in the transaction log file, in bytes
• FSeqNo — The VLF sequence number
• Status — Whether the VLF is active or not (0 = inactive, 2 = active, 1 – is not used)
• Parity — current parity bits (64 or 128, or 0 if the VLF has never been active)
• CreateLSN — the LSN when the VLF was created (0 = the VLF was created when the transaction log file was initially created). All other VLFs added after the initial creation of the transaction log file will have non-zero CreateLSN.

We can execute the following command for the DBTest2014 database, that we have created earlier:

DBCC LOGINFO (N'DBTest2014');
GO

See the result:

DBCC SQLPERF (LOGSPACE)

The only way in Transact-SQL to examine the amount of the log used is DBCC SQLPERF. The syntax for the command is:

DBCC SQLPERF
(
     [ LOGSPACE ]
     |
          [ "sys.dm_os_latch_stats" , CLEAR ]
     |
     [ "sys.dm_os_wait_stats" , CLEAR ]
)
     [WITH NO_INFOMSGS ]

The command returns a result set with one row per database:

• Database Name
• Log Size (MB)
• Log Space Used (%)
• Status: always set to zero

In my environment, the following command:

DBCC SQLPERF (LOGSPACE);
GO

Returns the following result:

In the next article, we are going to examine log records.

Tags: sql server, transaction log Last modified: October 06, 2022