Transaction log Truncate vs Shrink

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

VLF active and 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:

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:

DBCC_LOGINFO

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:

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:

SHRINKFILE_BEFORE

After executing SHRINKFILE command, the number of VLFs has decreased:

SHRINKFILE_AFTER

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”.

Posted in Transaction log
5 comments on “Transaction log Truncate vs Shrink
  1. Anon says:

    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?

    • Vedran Vedran says:

      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

  2. Tom Brauch says:

    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

  3. Frank says:

    I have to echo Tom’s response.

    No question this is the best explained (on topic) article I’ve come across to-date.

    Cheers!

  4. Gagan says:

    Great article!! Keep it up..

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

M.Sc. Vedran Kesegić

M.Sc. Vedran Kesegić

A Random Thought

Backup system databases (master, msdb, model) in addition to user databases. Without system db backup you will lose server-level objects (e.g. logins, jobs, maintenance plans...).