Why does my database using the simple recovery model have such a large transaction log?

Question

Not a duplicate with:

• Transaction Log is big! But simple recovery mode is chosen!

• Why is my transaction file growing when db is in simple mode and there are no waiting transactions

Reason: My log is not big, but empty. It's big, but in use.

---

The Problem

I have a small database (100 MB) which gets hit with a lot of small transactional workloads throughout the day. I've watched the transaction log continue to grow despite seemingly understanding how to cap it. Clearly I'm missing something.

• The database is in simple recovery model (I can't guarantee it wasn't in Full at one point when it was first created, but it has never been put under load in Full).

• DBCC SQLPERF(Logspace) says:

| Log Size (MB) | Log Space Used (%) | Status
--------------------------------------------
| 927.2422      | 94.72562           | 0

• DBCC LOGINFO says:

There are 39 rows (VLFs), all of which have a Status of 2.

• DBCC opentran says:

Oldest active transaction:
SPID: 51
Name: user_transaction
Start time : Mar 11 2019 7:27 PM

note: Given my server time, this transaction is about five minutes old.

---

I can't understand why every VLF is in use when my application only creates - at most - about a half dozen short-lived transactions, albeit very frequently.

I've tried killing the sole application process which connects to this database, but it didn't free up the transaction logs.

Workaround

I did a full backup just to scratch that itch and it freed up the entire log space... so... I don't know. I'll have to see if it grows excessively again.

Answer 1

Your first stop for problems like this should always be this query:

SELECT log_reuse_wait, log_reuse_wait_desc
FROM sys.databases 
WHERE [name] = 'YourDatabaseName';

From the sys.databases documentation:

Reuse of transaction log space is currently waiting on one of the following as of the last checkpoint.

There's a list of possible values on that page, and a fair amount of additional detail for the different values here: The Transaction Log (SQL Server) - Factors that can delay log truncation

It's possible that, if your server is not very busy, that it had been a while since an automatic CHECKPOINT ran. This is the process that writes data pages to disk after they have been modified in memory. Operations in the transaction log can't be overwritten until they have been successfully flushed to the data file, so you end up with a log_reuse_wait of 1 = Checkpoint.

Since you're using the simple recovery model, this seems like one of the more likely culprits (since you ruled out long-running transactions). Running a full database backup causes a CHECKPOINT to run, which is likely what caused the log file to finally clear for you after running the backup. From Database Checkpoints (SQL Server), specifically about the "Internal" checkpoint type:

Issued by various server operations such as backup and database-snapshot creation to guarantee that disk images match the current state of the log.