Welcome back. If you want to be job-ready as a DBA, you must understand the transaction log.
Most production “emergency” DBA tickets involve one of these:
log growth, failed log backups, replication/AG log send lag,
long-running transactions, or recovery taking too long.
The transaction log is not optional and it is not “just another file.”
It is the core mechanism SQL Server uses to guarantee durability, recoverability, and consistency.
Lesson description: You will learn what the transaction log stores (log records),
how SQL Server writes to the log (mostly sequential), what Virtual Log Files (VLFs) are and why too many VLFs hurt,
and what “log truncation” actually means (and what it does not mean).
You will also learn the real-world causes of log growth, how to identify what is preventing truncation,
and safe DBA actions to restore control without panic changes.
Every SQL Server database has at least two file types:
data file(s) (.mdf/.ndf) and a log file (.ldf).
The data files store the final version of the data pages.
The log file stores a record of changes that have happened (or are happening).
Durability rule: When you commit a transaction, SQL Server must ensure the log records for that
transaction are safely written to disk (log) before the commit is acknowledged.
This is why log performance is critical.
SQL Server writes log records describing operations such as:
Log records allow SQL Server to:
DBA mindset: Data pages can be written to disk later (lazy writing), but log records must be written
at commit time. That’s why log storage must be stable and fast.
SQL Server follows Write-Ahead Logging:
This ensures SQL Server can always recover, even if the server loses power at the worst moment.
Practical consequence: If your log disk is slow, commits become slow. Users feel it immediately.
In general, SQL Server writes the transaction log sequentially: it appends log records.
Sequential writes are efficient and predictable—until something breaks that pattern (like excessive fragmentation
at the storage layer or too many small growth events).
Key DBA point: Most log performance problems come from:
A transaction log file is internally divided into chunks called Virtual Log Files (VLFs).
SQL Server doesn’t treat the log as one big continuous region; it uses VLFs to manage log reuse.
DBA rule: Avoid “death by 1 MB autogrowth” (or similar tiny growth settings).
Pre-size logs and use reasonable growth increments.
This is where many beginners get it wrong.
Truncation means SQL Server marks inactive portions of the log as reusable after the log records are no longer needed.
The log can then reuse that space for new log records.
Truncation does not reduce the physical size of the .ldf file.
The file size stays the same; it just has reusable space inside.
DBA takeaway: A log can be “truncating fine” and still be 200 GB in file size
because it grew earlier and was never shrunk (and usually should not be shrunk routinely).
Recovery model determines how much of the log must be kept and what is required for truncation:
DBA warning: Switching recovery model without understanding business requirements is risky.
DBAs change recovery model only with clear approval and backup plan changes.
A log file grows when SQL Server needs more log space than is currently available as reusable.
Common causes:
DBA mindset: You do not “fix log growth” by shrinking first. You first identify what is preventing reuse.
SQL Server exposes a clear clue: log_reuse_wait_desc.
This tells you what is currently preventing log truncation.
SELECT
name,
recovery_model_desc,
log_reuse_wait_desc
FROM sys.databases
ORDER BY name;
How to interpret (examples):
NOTHING = log can truncate/reuse normallyLOG_BACKUP = Full/Bulk-Logged requires log backup; none occurred yetACTIVE_TRANSACTION = a transaction is still open; log can’t clear past itREPLICATION / AVAILABILITY_REPLICA = log must be retained for those featuresCHECKPOINT = SQL Server waiting for checkpoint-related reasons (usually temporary)This command returns log size and percent used for the current database context.
Run it for a database you want to inspect.
-- Replace YourDatabaseName with the target database USE master; GO DBCC SQLPERF(LOGSPACE); GO
DBA note: This output is extremely useful during “disk filling up” incidents.
SQL Server provides a modern DMV for VLF inspection: sys.dm_db_log_info.
This is read-only and safe.
-- Replace YourDatabaseName with a real database name
USE YourDatabaseName;
GO
SELECT
COUNT(*) AS VLF_Count,
SUM(vlf_size_mb) AS Total_VLF_Size_MB
FROM sys.dm_db_log_info(DB_ID());
Interpretation: You want a reasonable VLF count, not thousands.
The “right” number depends on size and workload, but extremely high VLF counts are a known operational smell.
DBA best practice: To prevent excessive VLFs, pre-size the log appropriately
and use sensible growth increments (not tiny autogrowth).
When the log file grows unexpectedly, follow this professional sequence:
log_reuse_wait_desc to find the blockage.LOG_BACKUP, verify log backup job is running and storage available.ACTIVE_TRANSACTION, identify the long-running transaction and evaluate safe actions.DBA warning: Shrinking the log repeatedly is usually harmful:
it fragments the file, increases VLF issues, and causes it to grow again under the next workload spike.
log_reuse_wait_desc.C:\DBA\Logs\section6-lesson4-log-reuse-wait.txtDBCC SQLPERF(LOGSPACE) and save output to:C:\DBA\Logs\section6-lesson4-logspace.txtsys.dm_db_log_info.C:\DBA\Logs\section6-lesson4-vlf-count.txtlog_reuse_wait_desc is your key clue for why truncation is blocked.Next, you will learn how SQL Server uses memory: the buffer pool, cache behavior, and memory grants for sorting/hash operations.
This is foundational for understanding slow queries, tempdb spills, and memory pressure in production.
Not a member yet? Register now
Are you a member? Login now