Virtual Log Files and impact on ConfigMgr performance

 

VLF (Virtual Log Files) can have a negative effect on database performance. Too many VLFs will slow down the startup, shutdown and read/write performance of any database.

VLFs are created when a transaction log file is allowed to auto-grow. While auto-growth will likely be enabled per database, the transaction log file should already be sized appropriately so it will not need to auto grow.

A general rule of thumb, there should be fewer than 50 VLFs.

The example illustrated here on SUSDB; may need to be applied to other databases such as the SCCM/ConfigMgr database.

There are scripts available that will list the VLF counts for every database. A simple way to test, using SQL Server Management Studio select the database in question and run the following:

DBCC LOGINFO;

Sample check of SUSDB, note we have a total of 115 VLFs (total records returned). Far too many.

clip_image001

Review log file size, right click on database, and view initial size (adjust this if needed):

clip_image003

 

Note: if you are going to have auto-growth enabled use a size such as 512MB or 1024MB; at least if the log file needs to auto-grow, you won’t have as many VLFS as it was set to 1MB!

To correct, we need to first shrink the transaction log file, then expand it to the proper size. Note: always do this when the database is lightly used, preferably after hours. The proper size of the transaction log (in this case) was 512MB.

USE SUSDB

GO

DBCC SHRINKFILE(SUSDB_log, TRUNCATEONLY)

ALTER DATABASE SUSDB

MODIFY FILE (NAME = SUSDB_log, SIZE=512MB);

GO

Now, to see the impact of the prior changes, execute the following:

DBCC LOGINFO:

There should be far fewer VLF’s created. We’ve reduced the count from 115 to 10.

clip_image004

As a side note, on this same server, I found that the SCCM database had over 626 VLFs, once the transaction log was shrunk and expanded, the VLF count was 26.

One final point; there is no need to have multiple transaction log files. Logging is sequential, you will gain no performance gains by having more than one log file.

References:

http://msdn.microsoft.com/en-us/library/ms179355(v=sql.105).aspx

http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx

http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

 

email

Written by , Posted .