Site: James Rhoat — Giving back to the community
Source: https://james.rhoat.com/p/transaction-log-management/
Title: Transaction Log Management
Description: A practical Q&A on SQL Server transaction log internals: how transactions work, crash recovery, why logs grow, and the correct way to shrink and resize the log file.
Date: 2021-01-12
Categories: sql-server, SQL Server transaction log management, why transaction log keeps growing, virtual log files VLF fragmentation, shrinking transaction log safely, ACID transactions in SQL Server
Word count: 1339
Reading time: 7 min

Contents:
- [What Is a Transaction?](#what-is-a-transaction)
- [Transaction Log Operation](#transaction-log-operation)
- [What Happens During a Crash?](#what-happens-during-a-crash)
- [Why Does My Log File Keep Growing?](#why-does-my-log-file-keep-growing)
- [How Do I Manage the Log File?](#how-do-i-manage-the-log-file)
  - [Why These Approaches Are Wrong](#why-these-approaches-are-wrong)
  - [Acceptable Solutions](#acceptable-solutions)
- [How Do I Get It Back to a Reasonable Size?](#how-do-i-get-it-back-to-a-reasonable-size)
  - [Resizing the Log File](#resizing-the-log-file)

***

# Transaction Log Management


I want to discuss some of the confusion around the transaction log. The transaction log is one of the most important things to understand in SQL Server. Especially when referring to high availability or disaster recovery. In these features, SQL uses the transaction log as a key component. After all, without your transaction log your database is unusable.

## What Is a Transaction?

A transaction is a single unit of work that complies with A.C.I.D. standards:

- **Atomic** -- It either all works or none of it does
- **Consistent** -- It complies with the "Rules", meaning constraints, triggers, datatype adherence, etc.
- **Isolated** -- It will not affect other transactions that are running at the same time
- **Durable** -- Changes are permanent and will survive a power outage, crash, and/or memory dump

Additionally, SQL Server uses implicit transactions. This means that it will apply the begin and commit/rollback for you if not specified. The line shown below is a single transaction:

```sql
DELETE FROM person WHERE lastname = 'Billy'
```

Whereas this is two transactions:

```sql
DELETE FROM person WHERE lastname = 'Billy'
DELETE FROM person WHERE firstname = 'Timmy'
```

To make this durable you need to wrap it with a `BEGIN TRANSACTION` and end with `COMMIT` or `ROLLBACK`. Otherwise known as an explicit transaction.

## Transaction Log Operation

To outline the transaction log operation:

1. User executes a transaction
2. Pages that are going to change go to the SQL cache on RAM
3. The log file records the details of the transaction and assigns the transaction a Log Sequence Number (LSN)
4. After storing the intent of the transaction, SQL Server then modifies the pages on RAM
5. The pages are, at some point, written back to the disk

## What Happens During a Crash?

If your server crashes, it is the responsibility of the log file to help you recover. The dirty pages (pages that were in RAM that did not make it to disk) are now lost. But the transaction log contains a full description of the intent of each transaction.

Upon restart, SQL begins to check integrity and consistency utilizing the log. To do this, it compares the LSNs in the log to those on disk. If the LSN in the log is newer it updates the file on the disk, known as the **REDO** phase. If the LSN in the log is older, it then knows it needs to rollback, known as the **UNDO** phase. These make sure that when the database comes online, it is consistent.

## Why Does My Log File Keep Growing?

To understand this, you need to first understand how the log file handles information. The transaction log file is a circular file. This means that it has a set amount of space and continues to create new log records sequentially until the end. Once it reaches the end of the file SQL Server faces two options:

1. Circle back to the beginning and override older log files
2. Grow the bounds of the log file and continue to consume space

With Option 2, it requires extra space and needs the overhead to grow the file. When the log file grows, it does so by creating new Virtual Log Files (VLFs) within the log. You can consider these as groups of transactions within the log. Thus using the transaction log with Option 1 is preferable.

So since this is preferable, why does SQL keep growing? SQL Server needs to keep log records around until no purpose remains for their storage. These purposes vary, but the main reason is the records are critical to the REDO and UNDO phase. SQL does this by marking them as "Active". This means that SQL is not allowed to reuse the space in the log until all the transactions are "Inactive" in the VLF. Then and ONLY then can SQL Server reuse the space allocated to the VLF.

So in short, your SQL Server is leaving the transactions as active. But don't freak out, this is normal for the FULL recovery model. In this model you need to take transaction log backups to fix this.

Unfortunately, this conversation usually doesn't happen right away. This comes up when the log file is using a massive amount of space on the drive.

## How Do I Manage the Log File?

There are many different articles about this, but they do not always have the best/correct answer. Most people just recommend to either:

1. Switching the database to Simple, shrink the file, switch back to Full
2. Truncating the log with `NO_LOG` / `TRUNCATE_ONLY`

Both of these solutions are harmful. Luckily, starting in 2008, option two is no longer possible. Yet, option 1 is still terrible. When you look at what we talked about earlier, I hope this makes you cringe.

### Why These Approaches Are Wrong

When you shrink the file this way you completely discard everything in the log file. That means all the work since the last backup is gone if your database crashes or fails for whatever reason. This violates the whole point of the FULL/BULK_LOGGED recovery models. The point of this model is to preserve the transaction log so a database can recover to a specific time.

Besides, if you shrink the file, it will grow again. This growth is likely to create log fragmentation in the VLFs. This is a performance concern much like disk fragmentation. Though it is not as much of a problem in SQL 2014 and above, it should still be on your mind. Paul Randal has a [good article about the creation of VLFs](https://www.sqlskills.com/blogs/paul/important-change-vlf-creation-algorithm-sql-server-2014/).

Additionally, this will pause your workload while the log file is growing.

### Acceptable Solutions

There are only two solutions that are acceptable in my opinion:

1. **Run in Simple mode** -- If you don't mind the possibility of losing data in the event of a disaster, this is a good option.
2. **Run regular log backups** -- Especially if you are looking for point-in-time recovery.

## How Do I Get It Back to a Reasonable Size?

It is always tricky to give the "reasonable" size answer. The truth is that it varies based on your workload and size of your database. I would always suggest the following:

- If it is your company's proprietary database, speak to the developers. They should have a staging environment where you can track the log growth.
- If you are using another company's database, reach out to their support. They might have an answer but they are more likely to give you the "it depends" response.
- Guess -- estimates range from 20-25% of the MDF to 1-2% on multi-terabyte databases.

### Resizing the Log File

To resize the log file I always use the following steps:

**Step 1:** Wait for an inactive time of day. It would be best to put the database into single user mode first but it is not required.

```sql
ALTER DATABASE databasename
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
```

**Step 2:** Run a transaction log backup if you are in full recovery mode. Or if you're using simple recovery, just clear the transaction log by running a checkpoint.

```sql
BACKUP LOG databasename
TO DISK = 'File path and extension (.TRN)';
```

**Step 3:** Shrink the log to the smallest possible amount.

```sql
DBCC SHRINKFILE(TlogLogicalFileName, TRUNCATEONLY);
```

You will need the filename of the log to complete this step. Run the following script if you do not know it:

```sql
EXEC SP_HELP
--or
SELECT DB_NAME() AS DbName,
    name AS FileName,
    size/128.0 AS CurrentSizeMB,
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;
```

**Step 4:** Alter the database to change the transaction log file size.

```sql
ALTER DATABASE [databasename]
MODIFY FILE
(
    NAME = [TlogLogicalFileName],
    SIZE = NewSize in MB
);
```

**Step 5:** If you set it to single user, change it back.

```sql
ALTER DATABASE databasename
SET MULTI_USER;
```

> **NOTE:** [SQLSkills suggests](https://www.sqlskills.com/blogs/paul/important-change-vlf-creation-algorithm-sql-server-2014/) growing the logs in 8 GB chunks using 8000MB as the NewSize variable. This creates VLFs that are 512 MB. These smaller chunks make it easier to maintain the smaller log file size.


---

## Related Posts

- [Volume Shadow Copy Service (VSS)](https://james.rhoat.com/p/vss-backups/page.md) — Jan 15, 2021: How VSS and the SQL Writer Service interact with SQL Server backups, including supported operations, limitations around point-in-time recovery, and common troubleshooting steps.
- [Vertical Partitioning](https://james.rhoat.com/p/vertical-partitioning/page.md) — Jan 14, 2021: How vertical partitioning (row splitting) reduces page waste and improves query performance in SQL Server, with a one-million-row demo.
- [Understanding Storage in SQL Server](https://james.rhoat.com/p/understanding-storage/page.md) — Jan 13, 2021: How SQL Server organizes data at the page and extent level, and why LOB, in-row, and row-overflow storage matter for performance and design.