Fixing a database with a high VLF count is a simple process:
1. Check the current size of the transaction log.
2. Backup the transaction log.
3. Shrink the transaction log to as close to 0 KB as possible.
4. Check that the VLF count is less than 50 (best if less than 10 at this point).
a. If the VLF count is greater than 50, repeat steps 1 through 3.
b. You may also have to check that there are no active transactions, etc. The log_reuse_wait_desc column in sys.databases will help identify why the log file cannot be shrunk.
5. Grow the transaction log back out to the original size
When I log into a new server environment for a client, it is pretty common to see multiple databases that have high VLF counts. The steps above are easy enough to complete when you have one or two databases that need fixed, but it becomes a very tedious process when there are 20, 30, 40 databases that all need fixed.
The output of the scripts below will come out similar to:
[code]
/****************************************
* Fix High VLF for database: MattWorks
* Starting VLF count: 219
****************************************/
-- Step 1: Get current log file size
use [MattWorks]
SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total Size in MB],size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB], type_desc
FROM sys.database_files;
-- Step 2: Take transaction log backup if needed, and then shrink the log file
DBCC SHRINKFILE (N'MattWorks_log' , 0, TRUNCATEONLY);
-- Step 3: Check if current VLF count is less than 50.
-- If not, take another log backup and repeat step #2 until VLF count is less than 50. check for active transactions, etc.
DBCC LogInfo;
--Step 4: Grow log file back to original size
USE [master];
GO
ALTER DATABASE [MattWorks] MODIFY FILE (NAME = N'MattWorks_log', SIZE = 1024MB);
GO
[/code]
