Basics of SQL Server Transaction Log

What is a Transaction Log?

There is a requirement in relational database systems that transactions must be durable. This is “D” in the ACID properties of transactions. The system must ensure that if a sudden crash happens, the transaction can be replayed. SQL Server fulfills this requirement by capturing all transactions in a physical file called a transaction log file.

In essence, every time a transaction is committed, SQL Server records changes produced by that transaction in a transaction log. Even if the transaction has not been persisted in the data file, it is available in the transaction log and can be replayed in the event of a sudden crash.

Recovery Models and Transaction Logs

SQL Server operates under three recovery models – Full, Bulk Logged, and Simple.

Under the Full recovery mode, ALL transactions are logged. Thus, the database can be fully recovered if a crash happens. This also means that the database backup can be restored to a specific point in time if the transaction or the related backup is available. Under Full and Bulk-Logged Recovery modes, transaction logs are truncated whenever there is a log backup executed.

Under the Simple Recovery mode, ALL transactions are still logged. However, the transaction log operation is truncated every time the database executes the checkpoint.

A checkpoint happens when SQL Server writes dirty buffers to the data file. Dirty buffers are essential pages stored in memory that have been changed by transactions, such as that the state in memory does not match the state in the disk. However, we won’t discuss this here. In the Simple Recovery mode, SQL Server captures all these changes in the Transaction Log to keep them until they are persisted.

The Transaction Log Structure

The transaction log is a physical file visible on the OS layer of the server where the SQL Server database is hosted. Each database has one transaction log, but it is possible to configure more. The thing is, having multiple transaction SQL logs does not bring any performance advantages. SQL Server writes to the transaction log sequentially – one file must be full before the next file gets in use. However, multiple files sitting on separate disks may save the day if the first file gets full.

Internally, the transaction log file is a series of virtual log files. The size and number of those files impact the time it takes to back up the database or bring it online. It is always a good idea to size the transaction log properly and ensure the auto-growth settings fit the level of activity expected. Then the file growths won’t happen too often.

What Makes the Log Grow?

Let’s start by creating a small database using the code in Listing 1. The data file is 4MB in size, the log file is 2MB to start with. Your production databases would never be this size especially with the popular practice of pre-allocation. We chose such sizes merely for demonstration purposes.

-- Listing 1: Create a Small Database

create database tranlogexperiment
on primary 
( name = N'tranlogexperiment', filename = N'C:\MSSQL\Data\tranlogexperiment.mdf', size = 4MB , FILEGROWTH = 1024KB )
log on
( name = N'Test1_log', filename = N'E:\MSSQL\Log\Test1_log.ldf' , size = 2MB , FILEGROWTH = 1024KB );
go

In that database, we create a single table for the further data manipulation language (DML) statements (Listing 2).

-- Listing 2: Create a Table

use tranlogexperiment
go
create table txn_log (
ID int
, FName varchar(50)
, LName varchar(50)
, CountryCode char (2)
)

By executing the code in Listing 3, we check and verify what we’ve done.

-- Listing 3: Check Recovery Model and File Sizes
select name, recovery_model_desc, log_reuse_wait_desc from sys.databases where name='tranlogexperiment';

select DB_NAME(database_id) [Database Name]
, type_desc [Database Name]
, name [Logical file Name]
, physical_name [Physical file Name]
, size*8/1024 [File Size (MB)]
, growth*8/1024 [File Growth (MB)]
from sys.master_files where database_id=DB_ID('tranlogexperiment');

Pay attention to the File size column. We proceed to invoke the transaction log growth by running INSERTs and DELETEs 100,000 times (Listing 4).

-- Listing 4: Create a Small Table
use tranlogexperiment
go
insert into txn_log values (1, 'Kenneth','Igiri', 'NG');
delete from txn_log where ID=1;
go 100000

Listing 4 inserts a single row into the txn_log table and deletes the same row, repeating this action 100,000 times.

Overall, the table does not grow due to this activity, but the transaction log grows significantly. When we repeat the query in Listing 3 after running the DML statement from Listing 4, we see how much the transaction log has grown:

The transaction log grew from 4MB to 40MB due to this activity even though the data file was not changed in size. This shows us clearly that the transaction log size has little to do with the data size. The impact on the size is from the activity (DML) happening on the database.

How Do We Manage Transaction Logs?

Database administrators who manage on-premises instances of SQL Server of IaaS installations should back up backing up the transaction logs regularly. It is helpful to have the disaster recovery configurations such as Log Shipping or AlwaysOn AG. Such configurations do the backups automatically. 

In Full recovery mode, a SQL Server log backup will truncate the transaction log portions that are no longer needed for recovery. The log truncation deletes inactive virtual log files. This way, it clears space in transaction logs for reuse.

The code in Listing 6 shows the size of the transaction log and how much free space we have in it.

-- Listing 6: Change Recovery Model
USE [tranlogexperiment]
GO
SELECT DB_NAME() AS [Database Name], 
    name AS [Logical File Name], 
    type_desc,
    size/128.0 AS [Current Size (MB)],  
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS [Free Space (MB)]
FROM sys.database_files
WHERE type IN (0,1);

We can also shrink the physical transaction log using the code in Listing 7. Before shrinking, make sure to have a backup of the transaction log. In production, it is best to schedule the log backups to avoid uncontrolled physical log file growth and ensure the data is preserved. With the disaster recovery option like Log Shipping or AlwaysOn AG configured, this is already granted.

We can query the log_reuse_wait_desc column on the sys.databases catalog view to determine any conditions that prevent the transaction log from being shrunk. Notice that we queried this column in Listing 3.

Such conditions could be a pending checkpoint, a pending log backup, ongoing backup or restore, an active long-running transaction, and similar activities in the database.

-- Listing 7: Change Recovery Model
USE [tranlogexperiment]
GO
DBCC SHRINKFILE (N'Test1_log' , 0, TRUNCATEONLY)
GO

We use the code in Listing 8 to back up the database. In our particular case, we first must make a full backup because log backups always reference a full backup. The “last” full backup starts the chain when dealing with the point-in-time recovery.

-- Listing 8: Backup Transaction Log
backup database tranlogexperiment to disk='tranlogexperiment.bkp';
backup log tranlogexperiment to disk='tranlogexperiment_log.trn';

When running a database in Simple Recovery mode, the transaction log gets truncated at every checkpoint. In this mode, log backups are not possible.

The location of the transaction log file should be sized properly to accommodate the long-running transactions that happen occasionally. Otherwise, the transaction log can still fill up the disk space. Figure 4 shows what happens to the log internally when a backup is taken. Notice that the physical file is still 40 MB, but we now have about 37 MB of free space.

What Happens in Simple Recovery Mode?

Now, let us set the tranlogexperiment database to Simple Recovery mode.  

-- Listing 9: Change Recovery Model
use master
go
alter database tranlogexperiment set recovery simple;

When we execute the code presented earlier in Listing 4, we’ll get slightly different behavior.

Figure 6 shows the transaction log growth in Simple Recovery mode when we execute the code in Listing 4. The size of the physical log file is just 15 MB. It is half less than it was under the Full Recovery Model earlier. Also, notice the free space of 11.5 MB.

Does it mean there was less log growth?

No. Figure 7 shows that while the session was in execution, our SQL Server also performed several checkpoints. This truncated the log and gave room for transactions to resume growing log at intervals.

Conclusion

The transaction log is an incredibly important component of a SQL Server database. It impacts everything that requires or relies on recovery – backups, restores, disaster recovery, and so forth. You can also log db activity.

In this article, we have discussed the nature of the transaction log, aspects of managing it properly, and demonstrated the behavior of DML in databases with Full or Simple Recovery modes in place. However, there is much more to learn about the transaction log. The entries in the references would be a good starting point for you.

References

1. Transaction log
2. SQL Server Databases & Storage

Read also

Importance of transaction log in SQL Server