Transaction log Truncate vs Shrink vs VLF number
Transaction log truncate – why it didn’t shrink my log ?
(Truncate vs Shrink)
Introduction
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.
Log Truncation
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:
|
1 |
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:
|
1 2 |
DBCC LOGINFO -- 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.
Log shrinking
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:
|
1 |
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:
Conclusion
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”.
Thank you for the great article.
By the way, can you help me understand “Log Space Used(%)” in DBCC SQLPERF(LOGSPACE) command.
i see it increase and decrease within matter of seconds. I thought the usage would never reduce until we backup the data. Why would the usage go down? what kind of logs are truncated?
For instance, on issuing “UPDATE TEST-TABLE SET TEST-COLUMN = 20”.
1. What are all the information that are actually logged?
2. Among them, what kind of data is permanent(it cannot be truncated until its backed up)?
3. What kind of data that will be truncated from log-file once this transaction completes successfully?
I’m glad to see a response.
During transaction, additional space is reserved in transaction log to guarantee that eventual rollback will succeed (because rollback also is logged). That is why after a commit you will see a drop in “Log Space Used(%)”, because after a commit that reserved space is released.
1) You can see exact log records with “select * from sys.fn_dblog(null,null)”, filtering that to just your transaction. You will see LOP_BEGIN_XACT (begin tran), LOP_MODIFY_ROW (changing data) for each row modified, and at the end LOP_COMMIT_XACT (commit tran).
2) That depends on the recovery model of the database. Generally, in full recovery model if anyone needs that log record (most commonly log backup needs them to backup them), they will not be truncated. In simple recovery model it is not needed as soon as transaction has finished and CHECKPOINT has occured.
3) By truncation data in log is not erased, and log file is not getting smaller. Truncation means VLF is flagged as free to be reused, so the log file does not need to grow (transaction log writer reuses that VLF to put new log records there, instead of log file growth). Conditions that VLF *can* be marked as free are several, but all of them really mean “nobody needs any log record from that VLF”. Complete list of the reasons you can find in this article, link to BOL explanation of “log_reuse_wait_desc” column.
Thank you for your response.
Vedran
Vedran,
I have read a lot of articles on truncating/shrinking sql logs, and I feel compelled to tell you that yours is excellent, in that, it’s perfectly clear in explaining and showing the difference between the two and leaves nothing out.
Thanks,
Tom
I have to echo Tom’s response.
No question this is the best explained (on topic) article I’ve come across to-date.
Cheers!
Great article!! Keep it up..
I so agree with the comments, this is the first article that really made me understand truncating… I have been a newbie, still consider myself one in the SQL DB arena and assumed that truncating the logs should make them smaller, this makes so much more sense now.
Thank you!
awesome article Vedran:)
Good Article.
It was really helpfull.
Tks!
I’m sorry but something is not clear.
Can you explain me the difference between Truncate Log and Checkpoint.
I’m asking because because I think that Checkpoint mark as reusable a VLF, not the Truncate.
maybe I did not understand well
Hi Vic,
You understand good that checkpoint marks VLFs as reusable, but that is only in SIMPLE recovery model. If database is in FULL recovery model, only LOG BACKUP will mark VLFs as reusable. If log backup job is not running, log might grow until it fills the disk.
Hi Vedran,
Thanks for great article, few more questions-
-How we can limit no of VLFs in any DB .
-what is meaning of below message and impact ?
Database xyz has more than 10000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.
-Does VLF count matters Insert , Update
Regards,
Brahma
Hi Brahma, thanks for the comment.
VLFs are logical sections of transaction log file that can be marked as “free” for rewrite or occupied (containing data somebody needs, cannot be rewritten). They are created when log file grows – newly occupied space on disk is divided into that logical pieces, VLFs. There is a certain rule how many VLFs will be created depending on growth step size, and that changes with version. General rule is you do not want percentage growth, but a decent growth step size in MB, eg 200-1000 MB, depending on your disk write speed and how long is acceptable that all user write activity is frozen (during log growth user writes freeze). Too big growth step therefore is also not good.
When you have “too many” VLFs, operations that examine transaction log will perform slower, like recovery phase (after failover db to become online takes longer, bigger downtime on SQL restart, slower database restore, backup, replication, CDC, rollbacks). Therefore it is not good to have thousands of VLFs, normal is around 100-300 but not a strict rule. INSERT/UPDATE/DELETE should not be affected, at least I’m not aware how since they normally should not scan all VLFs.
You probably do not want to stop your log from growing, just to make number of VLFs lower. You can do that by first shrinking the log to as close to zero as possible, then manually grow the log in big steps, eg 4-20 GB steps, until desired log size is reached, so it does not need to grow if all is normal.
Hi Vedran,
Thanks for valuable article , but when transaction staus is active and its running from more than a day. the above task does not work .Like (see below image for reference )
Sorry I am unable to upload Image