How to Fix Databases with High VLF Count

In our earlier posts on recovery, we’ve covered the importance of properly managing SQL Server Transaction Log file growth. This includes how to detect databases with a high VLF count. It can be time-consuming to manually fix high VLF count databases. To help with that pain, this post provides a script automatically reduces the number of Virtual Log Files. It’s designed to run on an as-needed-basis once you’ve identified a database with a high VLF count; typically above 2,000.

Elks have no need to fix high VLF count databases

   Elks have no need to fix high VLF count databases

The script shrinks the existing log file down to the smallest size possible, then grows it back to the prior size using a sensible growth increment. If the database is in simple recovery model, the script runs a checkpoint command to attempt to force transaction log truncation before growing the file. If the database is in full recovery model, one or more transaction log backups are taken to the NUL: device, in addition to the checkpoint operation, to allow the necessary transaction log truncation to occur. Ensure you take and test a full backup of all databases you run this script against, prior to running the script. For databases in full recovery model, you’ll need to take a database backup after the script runs since the prior log chain will be broken by the log backups that have been take to the NUL: device.

Ads by Google, Paying the Rent:

NOTE: Please do not run this script against databases involved in an Availability Group, Database Mirroring, Log Shipping, or Transactional Replication unless you’re prepared to deal with the potential need to recreate the secondary database(s). Shrinking a log file is not recommended unless you have a clear and present need to fix a problem, such as a very large VLF count that might negatively affect recovery.

The output for a database in simple recovery model looks like:

This script will shrink the log file, then grow it back to the original size with an efficient growth increment.  This is done to lower VLF count.
Current VLF Count is: 2970
Log has been shrunk and re-grown back to its original size.  VLF Count is now: 95
Original size: 29430, Current Size: 28672

For a database in full recovery model, you’ll see evidence of the required log backup actions being taken:

This script will shrink the log file, then grow it back to the original size with an efficient growth increment.  This is done to lower VLF count.
Current VLF Count is: 2970
Processed 1010 pages for database 'db', file 'db_log' on file 1.
BACKUP LOG successfully processed 1010 pages in 0.023 seconds (342.815 MB/sec).
2019-04-06 15:27:38: Waiting for VLF truncation.
Processed 1 pages for database 'db', file 'db_log' on file 1.
BACKUP LOG successfully processed 1 pages in 0.004 seconds (0.854 MB/sec).
Log has been shrunk and re-grown back to its original size.  VLF Count is now: 95
Original size: 29430 MB, Current Size: 28672 MB
Msg 50000, Level 14, State 1, Line 200
THIS IS A WARNING: A fake log backup has been taken in order to reduce the size of the active portion of the transaction log.
Please ensure you immediately take a full backup of the database, along with a log backup to re-establish an appropriate recovery chain.

Note the red text above is not actually an error – the text is red to ensure you pay attention to the advice to take a database backup.

Let me know if you have any questions about this post in the comments, below.

This post is part of our series of posts on recovery. Also check out our list of SQL Server Tools.

Check the Microsoft Docs for further reading about the Transaction Log, and details about Virtual Log Files.

Ads by Google, Paying the Rent: