Transaction log Truncate vs Shrink vs VLF number
Transaction log truncate – why it didn’t shrink my log ?
(Truncate vs Shrink)
One term that makes the most confusion between people dealing with sql server transaction log is “log truncation”. In this article I’ll explain what log truncation is and the difference between log truncation and the log shrinking. Everything said here applies to all recovery models (simple, full, and bulk logged) unless otherwise specified.
If you look-up the word “truncate” in the Cambridge dictionary, it says “To make something shorter or quicker, especially by removing the end of it“. It is no wonder that many people think log truncation will chop-off the end of the transaction log, making it smaller – very wrong! “Log truncation” is a poorly chosen term for marking parts of the log free for reuse. Obviously, marking something (changing a bit that says is something free or not) certainly will not change the file size. So, the term is a kind of misnomer. Much better term to use is “log clearing“. It does not overwrite parts of the log with zeroes, it just clears the bit to say “this part of the log is not needed anymore and can be reused/overwritten”. Overwriting with zeroes happens only on log growth for the newly allocated part and cannot be avoided. Even instant file initialization cannot avoid zeroing, and there is a good reason for that (not because of security, but because of functionality – I’ll explain in some other post).
Log file is internally logically divided into smaller chunks called Virtual Log Files (VLF). Each VLF is the smallest (and the only) unit that can be marked as available for reuse (free) or not available for reuse (used). If VLF contains one or more log records that are needed by somebody and thus must not be overwritten, the entire VLF is marked “active” (used). If VLF does not contain any records that are needed by anyone, entire VLF is marked as “inactive” (free) during log truncation process. “Inactive” means VLF can safely be reused / overwritten.
Log truncation means “marking VLFs as free“
The process of marking active VLFs as inactive does not occur all the time the log is written to because it would be too often and it does take resources. It occurs only on CHECKPOINT if we are in simple recovery model, and only on LOG BACKUP if we are in full or bulk logged recovery model. It is very important in full recovery model to set a periodic log backup, because otherwise log clearing process will never occur, thus once active VLF will never become inactive and your log will grow until the disk is full. Log clearing process inspects active VLFs and it can happen that no VLFs can be marked as inactive because none of them met the required conditions to be cleared.
WHY my log file is not smaller after DBCC SHRINKFILE ?
The reason is written in log_reuse_wait_desc column of sys.databases view. Query it:
SELECT name, log_reuse_wait_desc FROM sys.databases
The conditions for an active VLF to become inactive during log clearing process in all recovery models are:
- VLF must not contain any part of active transaction (uncommitted). Beware of long running transactions. log_reuse_wait_desc in sys.databases is ACTIVE_TRANSACTION.
- checkpoint has not occurred after that VLF. For recovery process we need log records of the changes that are not yet hardened into data files. log_reuse_wait_desc is CHECKPOINT.
In full and bulk logged recovery models there are additional conditions:
- Log backup – all the log records i that VLF are backed-up. log_reuse_wait_desc is LOG_BACKUP.
- VLF does not contain any log records needed by some process (transactional replication log reader agent, mirror log reader agent)
For a complete list of conditions that must be met see the BOL on sys.databases column log_reuse_wait_desc.
List your Virtual Log Files (VLF)
You can inspect a list of VLFs in your transaction log by issuing a command DBCC LOGINFO. Here is example output:
-- SELECT * FROM sys.dm_db_log_info(NULL) -- new function
Each row represents one VLF, so in our example we have 7 VLFs in the transaction log. FileSize is the size of each VLF in bytes. If you sum them all and add 8KB header (you can see that first VLF offset is always 8192 bytes, not zero) you will get the exact log file size. Status is the most interesting for us at the moment: value of 2 means “ACTIVE”, and 0 is inactive (free). There will always be at least one active VLF, the one log writer is currently writing to.
When the log writer bumps to the end of the log file, it will wrap-around and start writing over the first free VLF (which automatically becomes “active”). If there are no free VLF’s that log writer can write to, the log file will, you probably guessed – GROW! It will grow by the amount specified in FILEGROWTH attribute of the database file. It is best practice to set that growth manually to fixed size, not percentage.
WARNING: Do NOT shrink the log below the size you know it will grow-over again in a week or a month! You are just wasting disk IO on shrink-and-grow-again game, and users will notice because all writers STOP while log grows and zeroes-out the new area!
Log shrinking actually can make log file smaller. But only if there are inactive VLF’s at the end of transaction log. The command is DBCC SHRINKFILE:
DBCC SHRINKFILE(2, TRUNCATEONLY)
Instead of truncateonly you can specify the new transaction log file size in MB you do not want to go below. “Truncateonly” is the same as zero. What this actually does is find last active VLF and chop-off the log file after it, making the log file smaller:
After executing SHRINKFILE command, the number of VLFs has decreased:
To summarize, log truncation is a misnomer and is better called “log clearing”. It will not shrink the log file at all because it just marks some VLFs as inactive. DBCC SHRINKFILE will actually truncate the log after last active VLF. That makes “log truncation” even worst term than just misnomer, because truncation does not occur in log truncation process at all and the real truncation occurs in completely different process (dbcc shrinkfile) often creating confusion among sql server newbies.
If the last VLF is active, you won’t be able to shrink the log. But log_reuse_wait_desc column in sys.databases will tell you the reason why that last VLF is still marked “active”.