Transaction log myths

There are some myths widely spread about transaction log that are to be debunked here:

  • Full/diff backup will clear the transaction log

FALSE. Only transaction log backup in full and bulk_logged recovery model, or checkpoint in simple recovery model will trigger the log clearing process. Full and diff backups will only defer it until the backup finishes. If you only do a full/diff backup in full recovery model, your log will grow until the disk is full.

  • Log truncate will make it smaller

FALSE. Log truncate just scans active VLFs (virtual log files) and those that are not needed anymore by anyone marks as inactive. That does not change the log file size.

  • Log shrink will make it smaller

It depends. DBCC SHRINKFILE on the log can only chop-off part of the log file after the last active VLF. If the last VLF in file is active, file shrink will not make the file any smaller.

  • Instant file initialization will speed-up log file allocations (growth, initialization)

FALSE. Instant file initialization only works for data files, not for log files. Log files must be zeroed-out because of recovery process. It helps the recovery process after a crash to find where the log records really ends: when it bumps into zeros or alternate 64/128 value.

  • Multiple log files will make performance gains

FALSE. Transaction log file is always written sequentially, in a single thread. Multiple files won’t parallelize log writing. However, you could use additional files if for example, you run out of space on current log disk to add a new log file on the other disk.

  • My database is in simple recovery model, so the log will not grow

FALSE. Transaction log is used in every recovery model, including simple. It will just be cleared more often (on each checkpoint, instead on each log backup) and some operations will be bulk-logged. It is important to properly size the transaction log even for databases in simple recovery model.

  • It is a good guideline to determine the log size as percentage of database size

FALSE. Log size is determined by the maximum amount of log generated between two log clearings. For example, small database that does lot of data changes (e,g, in-place updates) can generate 100x more log than database size. Log must also fit the biggest transaction, e.g. biggest table rebuild.

  • Regular log shrinking is a good maintenance practice

FALSE. Log growth is very expensive because the new chunk must be zeroed-out. All write activity stops on that database until zeroing is finished, and if your disk write is slow or autogrowth size is big, that pause can be huge and users will notice. That’s one reason why you want to avoid growth. If you shrink the log, it will grow again and you are just wasting disk operation on needless shrink-and-grow-again game. You could shrink after some extraordinary event that blew-up log size, but never shrink to a size smaller than is normal operational size for that database.

  • TRUNCATE TABLE is not logged

FALSE. Extents (and up to 8 pages in mixed extents, per table/index/partition) are deallocated from the table and that is logged. But it generates far less log than DELETE command.

  • Minimally logged operation can only happen in BULK_LOGGED recovery model

FALSE. It can occur in SIMPLE mode also.

  • Every change is logged, regardless of recovery model

TRUE with one exception. The only operation that is not logged is on one part of tempdb database called row version store, used for row versioning.

  • Log records are written to disk immediately

FALSE. There is a 60KB log cache in the RAM that log records are written to, not the disk. That cache is written to the disk, to the transaction log file, as soon as all 60KB is filled, any session of that database issues a commit (a real commit, not the nested one that just decreases @@TRANCOUNT), and on some other events. All log records flushed together to the disk will have the same block offset (LSN consists of VLF number, block offset, and log record number). Therefore, log block cannot be larger than 60KB (cache’s size).

Posted in Transaction log
3 comments on “Transaction log myths
  1. Luka Gros says:

    Great post Vedrane! Hooe to read from you more :)

    • Vedran Vedran says:

      Thanks Luka, I really appreciate your comments. I plan to write a few more articles about VLF-s to round-up transaction log theme.

  2. Uwe Ricken says:

    Hi Vedran,

    pretty good summary of the myths around LOG. I assume they will be restistent :)

    Best from Germany, Uwe

    BTW: If someone is familiar with german language …
    I’ve written a very detailed blog article about how TRUNCATE TABLE works…
    http://db-berater.blogspot.it/2013/06/truncate-table-wird-nicht-protokolliert.html

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 of corrupted database is worthless. If you do not run regular DBCC CHECKDB, your backups might be worthless.