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.