SQL Virtual Log Files (VLFs)
Overview
The VLFs report shows the inside of a database’s transaction log: the virtual log files SQL Server carves it into, drawn as a ring with one arc per VLF.
A ring rather than a bar chart, because the transaction log is a ring. SQL Server fills VLFs head to tail and then wraps back to the beginning of the file. Position around the circle is offset into the file, so the wrap point becomes something you can point at rather than something you have to picture.
Three things are marked outside the ring, and they are the three the grid cannot show at a glance:
- The active log, as a solid arc. This is what SQL Server still needs.
- The reclaimable tail, as a dashed arc. This is what
DBCC SHRINKFILEwould release, and it is the number the Shrink to box on the toolbar is filled from. - The head, as a tick with a dot. Everything wraps from there.
Where to find it
A database-level report. Expand the server → expand the database → Real Time → VLFs.
Requires SQL Server 2012 or newer. Older versions return a different set of columns from DBCC LOGINFO, and the report says so rather than guessing. It is also unavailable on AWS RDS running SQL Server 2016 or older, which blocks DBCC LOGINFO and predates sys.dm_db_log_info.
Reading the ring
Arc colour is the VLF’s state.
| Colour | State | Meaning |
|---|---|---|
| Amber | In use | Part of the active log. SQL Server cannot reuse or release it. |
| Green | Reusable | Written to, since backed up or truncated. Free to be overwritten. |
| Pale | Never used | Created and never written. Usually the result of a growth. |
Arc width is the VLF’s size, as a share of the file. A log grown evenly draws even arcs; a log created large and then grown in small steps draws a few fat arcs and a fringe of hairlines, and that fringe is the growth history.
The hub carries the VLF count, the log size, and a one-word verdict on the count:
| Count | Verdict |
|---|---|
| Under 50 | Healthy |
| 50 to 199 | Watch |
| 200 to 999 | High |
| 1,000 or more | Very high |
Past a few hundred VLFs the arcs stop being separable, so adjacent ones are merged into bins. A bin takes the most active state it holds – a bin containing anything in use is drawn as in use – and the tooltip says how many VLFs it stands for.
The size profile
Beside the ring, how many VLFs there are of each distinct size. This is the growth history of the file, and nothing else in the product shows it.
- One bar – the file was sized once and has not been grown since. This is the goal.
- Two bars – a create plus one growth setting.
- A dozen thin bars – percentage autogrowth. Every growth is larger than the last, so the sizes never repeat.
The line under the profile names the growth increment when a large number of VLFs share one small size, because that increment is the thing worth changing. The VLF count is the symptom; the autogrowth setting is the cause.
Strip
Ring and Strip on the toolbar draw the same model two ways. Strip unrolls the ring into a horizontal band with an offset axis along the bottom, for when a linear reading of “how far into the file” is what you want. The choice is remembered between visits.
Why VLF count matters
Every VLF is a unit of work at recovery time and at log backup time. A log with thousands of them makes:
- Database startup and crash recovery slower, because recovery walks the VLFs.
- Log backups slower, for the same reason.
- Log truncation more granular than it needs to be.
The count is not a performance problem in the way a missing index is. It is a time to recover problem, and it shows up on the day you can least afford it.
Why a shrink often releases nothing
DBCC SHRINKFILE on a log file can only give back the run of free VLFs at the end of the file. A free VLF in the middle is not reclaimable, because the file cannot have a hole in it.
That is why the Reclaimable column and the dashed arc only ever cover the tail. If the active log happens to sit near the end of the file, there is nothing past it to release and a shrink will do nothing at all – and the report says so in the subtitle rather than letting you find out by running it.
The fix in that case is a log backup (or, in SIMPLE recovery, a checkpoint), which frees the VLFs behind the head and lets the head wrap round to the start of the file. Then the shrink has somewhere to work.
Shrinking and growing
The toolbar acts on the log file named in it. On the usual single-log-file database that is the only one; a database with two log files gets a picker.
Shrink to is pre-filled with the file size less the reclaimable tail – the smallest size the shrink is likely to reach in one go. If you type something smaller, the confirmation says where the active log ends, because the shrink will stop there.
Grow to is pre-filled with a quarter more than the file is now. Growing the log back in one step after a shrink is the point: one large growth creates a handful of large VLFs, where the same distance covered by autogrowth in small increments creates hundreds of small ones. That is how most logs end up with a four-figure VLF count.
Script It builds both statements with a comment explaining each, ready to take away and run in a maintenance window instead. The same script is on the right-click menu, and can be copied straight to the clipboard.
The grid
| Column | What it is |
|---|---|
| VLF | Position in the file, 1 upwards. |
| Size (MB) | The VLF’s size. |
| Status | In use, Reusable, or Never used. |
| Sequence No | The log sequence this VLF was last written in. Blank means never. The highest value in the file is the head. |
| Starts At (MB) | How far into the file this VLF begins. |
| Start Offset | The same in bytes, as the source reports it. |
| Reclaimable | Whether a shrink would release it. |
| Log File | Which log file it belongs to. |
Clicking an arc selects its grid row; selecting a grid row highlights its arc.
How to read the report
- Read the hub first. The count and the verdict are the headline.
- Look at how much of the ring is amber. A thin amber run on a large ring means most of the file is doing nothing.
- Follow the dashed arc. That is what a shrink would give back. If there isn’t one, a shrink is not the answer today – a log backup is.
- Read the size profile. If a few hundred VLFs share one small size, that size is your autogrowth setting and it is the thing to change.
- Shrink, then grow back in one step. Leaving the log to autogrow back is what produced the count you started with.
Common patterns
Thousands of tiny VLFs, one dominant small size in the profile. Autogrowth is set too small. Shrink, set a sensible fixed growth increment, and grow the file back in one operation.
A dozen distinct sizes, none repeating. Percentage autogrowth. Change it to a fixed size in MB.
Almost the whole ring is amber. The active log fills the file. Log backups are not running often enough, or a long-running transaction is holding the log. See Backup Status.
Almost the whole ring is pale, with a short amber run. The file was grown well past what it needs, and the space has never been written. Safe to shrink, and worth asking what grew it.
Nothing reclaimable even though most VLFs are free. The head is sitting near the end of the file. Back the log up and look again.
Related reports
| Report | Why you would go there |
|---|---|
| Files | The same log file’s size and latency alongside the data files. |
| File Utilization | Every file on the instance, grouped by volume. |
| Filesize Over Time | What the log has been doing over days rather than right now. |
| Backup Status | Why the active log will not shrink. |
| Disk Space | Whether the volume can take the growth you are about to allow. |
Frequently asked questions
How many VLFs should I have? Under 50 is comfortable and a few hundred is survivable. Past a thousand, recovery and log backups are measurably slower. There is no exact right number – the count matters relative to the size of the file.
I shrank the log and nothing happened. Why? Almost certainly nothing was reclaimable. DBCC SHRINKFILE can only release VLFs at the end of the file, and if the active log reaches the end there is nothing past it. Back the log up and try again.
Should I shrink my log file at all? Only if it grew to a size it does not need and will not need again. A log big enough for your busiest operation is a log sized correctly. Shrinking one that will just grow back costs you the growth events and gives you more VLFs than you started with.
Why does growing the file back matter after a shrink? Because how you grow it decides how many VLFs you get. One growth of 8 GB creates a small number of large VLFs; 8 GB reached in 64 MB autogrowth steps creates well over a hundred.
What is the dot on the outside of the ring? The head – the end of the VLF with the highest sequence number. Writing continues from there and wraps back to the start of the file.
My database has two log files. Which one does Shrink act on? The one named in the toolbar picker. Clicking an arc or a grid row also points the toolbar at that file.