Checking For Internal Log File Fragmentation

One of the items on my checklist of things a DBA should check for when starting at a new company is internal log file fragmentation – log files that have too many virtual log files (VLFs) in them. (See Step 8 of this post for more info on virtual log files.) This is something I do as a background task. Every couple of months and whenever I have some spare time, I’ll check the log files on my servers for excessive amounts of VLFs. Unfortunately, spare time is becoming a rare commodity lately and I did not want this task to keep getting pushed back or neglected. I needed a way to automate this.

I came up with the script shown below. I created the stored procedure in the master database of all my 2005 and 2008 servers. Sorry, it won’t work on 2000 servers – it uses Database Mail and the sys.databases system table. You could rewrite it to use SQL Mail and sysdatabases instead, but I haven’t bothered to do this because we are on track to migrate our existing SQL 2000 servers to 2008 in a month or two anyway. I’ve scheduled a job to execute this procedure once a week. It will count how many virtual log files are in the transaction log for each database on the server and will send an email if the count is over a certain threshold. This script will not automate the cleaning up of VLF fragmentation, but it will do the checks for me and let me know which databases need attention.

USE master

CREATE PROCEDURE sp_VLFCheck

AS

/*

This procedure checks the transaction log of each database on the server and
emails a report if any contain more than a specified number of virtual log files
(as defined in the @MaxVLFs variable). Excessive VLFs can result in poor performance.

- Shaun J. Stuart

*/

DECLARE @SQLCmd varchar(40)
DECLARE	@DBName varchar(100)
DECLARE @DBID int
DECLARE	@MaxVLFs smallint
DECLARE	@VLFCount int
DECLARE	@EmailSubject varchar(255)
DECLARE	@EmailRecipients varchar(max)
DECLARE	@EmailBody varchar(max)

SET	@EmailSubject = 'Excessive VLFs found on ' + @@servername
SET	@EmailRecipients = '<enter email address here>'
SET	@EmailBody = 'Transaction log files with excessive VLFs have been found. '
SET @MaxVLFs = 50		/* threshold for number of VLFs */

DECLARE DBNameCursor CURSOR FOR
	SELECT	database_id
	FROM	sys.databases
	WHERE	state = 0			/* online databases only */

DECLARE @LogInfo TABLE (
	FileId tinyint,
	FileSize bigint,
	StartOffset bigint,
	FSeqNo int,
	Status tinyint,
	Parity tinyint,
	CreateLSN numeric(25,0)
)

IF  OBJECT_ID('tempdb..##ManyVLFsFound') IS NOT NULL
	DROP TABLE ##ManyVLFsFound

CREATE TABLE	##ManyVLFsFound (
	dbname varchar(100),
	NumOfVLFs int
)

OPEN DBNameCursor
FETCH NEXT FROM DBNameCursor INTO @DBID
WHILE @@fetch_status = 0
BEGIN

	SET @SQLCmd = 'DBCC LOGINFO (' + convert(varchar(5),@DBID) + ') WITH NO_INFOMSGS'
	INSERT INTO @LogInfo EXEC (@SQLCmd)

	SELECT @VLFCount = COUNT(*) FROM @LogInfo

	IF @VLFCount > @MaxVLFs
	BEGIN
		SELECT	@DBName = name FROM sys.databases WHERE database_id = @DBID

		INSERT INTO ##ManyVLFsFound
			VALUES	(@DBName,
					@VLFCount)
	END

	DELETE FROM @LogInfo
	FETCH NEXT FROM DBNameCursor INTO @DBID

END

CLOSE	DBNameCursor
DEALLOCATE	DBNameCursor

IF (SELECT COUNT(*) FROM ##ManyVLFsFound) > 0
BEGIN
	EXEC msdb.dbo.sp_send_dbmail	@recipients = @EmailRecipients,
						@subject = @EmailSubject,
						@body = @EmailBody,
						@query = 'SELECT NumOfVLFs, DBName FROM ##ManyVLFsFound'

END

DROP TABLE	##ManyVLFsFound

Enter the notification email address on line 27. Lines 26 and 28 contain the subject line and message text you want to include in your email. The email will also contain the results of a query that will list the database name and the number of VLFs that were found. The VLF count threshold is set in line 29. I’ve chosen a value of 50. Typically, you want to have no more than 20 or 30 VLFs for best performance. I set my threshold a bit higher, based on what I’ve seen on my servers.

This script assumes you have a public default mail profile set up in Database Mail (or a private one that is used by whatever account executes this stored procedure). It also assumes one log file per database. (This is all you should have. There is no performance increase to be gained by having multiple logfiles – source.)

The first time you run this script, you may see many databases with a large number of VLFs. This typically means your log file has autogrown many times. While you are cleaning up the VLFs, it would also be a good opportunity to check your autogrowth settings (I recommend setting it to grow by a fixed amount, not a percentage) and to size your log files appropriately so that autogrowth is kept to a minimum.

Note: I’m testing out a new plug-in to list code. If you just highlight the above text, right click, and choose Copy, you’ll get the line numbers included. Instead, hover your mouse over the code and use the icons that appear in the upper right portion of the code listing. The second icon will copy the code to your clipboard, but this method does not preserve the formatting. Try the first icon instead. This will open a new window with only the code, which you can then copy.

7 thoughts on “Checking For Internal Log File Fragmentation

  1. when i run this script on 2008 R2 and add my email adddrss i get this error: any ideas?
    Msg 111, Level 15, State 1, Procedure sp_VLFCheck, Line 18
    ‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch.

  2. Oh..You probably have to remove the USE MASTER line. Just manualy switch to that database before running the script.

  3. yup caught that. thanks for the reply. Also depending on how the server collation is configured there is an issue on line 69 with case sensitivity. That variable is specified in all lowercase and should match how it is specified above where it is declared. MIght want to edit that :)

    Thanks for the comments!

Leave a Reply to David Cancel reply

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

I am a real person and can prove it by doing this math problem: (required) Time limit is exhausted. Please reload CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.