{"id":230,"date":"2018-06-09T21:02:53","date_gmt":"2018-06-09T21:02:53","guid":{"rendered":"http:\/\/dbtut.com\/?p=230"},"modified":"2019-11-12T11:42:47","modified_gmt":"2019-11-12T11:42:47","slug":"vlfvirtual-log-file-count","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/06\/09\/vlfvirtual-log-file-count\/","title":{"rendered":"Vlf(Virtual Log File) Count"},"content":{"rendered":"

What is\u00a0Vlf(Virtual Log File)?<\/h1>\n

Vlf (Virtual Log File) is a virtual file, as the name implies.<\/p>\n

The Transaction Log file with the ldf extension in SQL Server consists of one or more virtual log files. <\/span><\/p>\n

If you create a database with default settings and the autogrowth and initial size of the database’s log file are not set appropriately, the number of virtual log files will be too <\/span>many <\/span>and will also cause performance bottlenecks.<\/span><\/p>\n

High virtual log file (vlf) count<\/h1>\n

It increases <\/span>the recovery time of the database. <\/span><\/p>\n

For example, if the server or sql server service is restarted, the recovery process wil be performed when the database opening. If the number of Vlf is too many, this increases recovery time.<\/p>\n

High number of vlf will also increase the log backup time. In some cases it also increases insert \/ update \/ delete times.<\/p>\n

For example, if you have a database that has 5 MB initial size and auto growth of the database is 10%, then performance bottleneck will start when the size of the log file grows over time. Because, %10 of the 5 MB is too less and this cause high number of VLF. You can find the vlf count of your transaction log using the DBCC LOGINFO () command.<\/p>\n

Find VLF Count<\/h1>\n

When you run the DBCC LOGINFO command, the number of records returned shows the number of VLFs in the log file. The number of VLF in my Test database is 4, as shown in the following screenshot.<\/p>\n

<\/p>\n

You can use Kevin Riley’s script to check which databases on instance have this problem.<\/p>\n

--variables to hold each 'iteration'  \ndeclare @query varchar(100)  \ndeclare @dbname sysname  \ndeclare @vlfs int  \n  \n--table variable used to 'loop' over databases  \ndeclare @databases table (dbname sysname)  \ninsert into @databases  \n--only choose online databases  \nselect name from sys.databases where state = 0  \n  \n--table variable to hold results  \ndeclare @vlfcounts table  \n    (dbname sysname,  \n    vlfcount int)  \n  \n \n \n--table variable to capture DBCC loginfo output  \n--changes in the output of DBCC loginfo from SQL2012 mean we have to determine the version \n \ndeclare @MajorVersion tinyint  \nset @MajorVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))-1) \n \nif @MajorVersion < 11 -- pre-SQL2012 \nbegin \n    declare @dbccloginfo table  \n    (  \n        fileid smallint,  \n        file_size bigint,  \n        start_offset bigint,  \n        fseqno int,  \n        [status] tinyint,  \n        parity tinyint,  \n        create_lsn numeric(25,0)  \n    )  \n  \n    while exists(select top 1 dbname from @databases)  \n    begin  \n  \n        set @dbname = (select top 1 dbname from @databases)  \n        set @query = 'dbcc loginfo (' + '''' + @dbname + ''') '  \n  \n        insert into @dbccloginfo  \n        exec (@query)  \n  \n        set @vlfs = @@rowcount  \n  \n        insert @vlfcounts  \n        values(@dbname, @vlfs)  \n  \n        delete from @databases where dbname = @dbname  \n  \n    end --while \nend \nelse \nbegin \n    declare @dbccloginfo2012 table  \n    (  \n        RecoveryUnitId int, \n        fileid smallint,  \n        file_size bigint,  \n        start_offset bigint,  \n        fseqno int,  \n        [status] tinyint,  \n        parity tinyint,  \n        create_lsn numeric(25,0)  \n    )  \n  \n    while exists(select top 1 dbname from @databases)  \n    begin  \n  \n        set @dbname = (select top 1 dbname from @databases)  \n        set @query = 'dbcc loginfo (' + '''' + @dbname + ''') '  \n  \n        insert into @dbccloginfo2012  \n        exec (@query)  \n  \n        set @vlfs = @@rowcount  \n  \n        insert @vlfcounts  \n        values(@dbname, @vlfs)  \n  \n        delete from @databases where dbname = @dbname  \n  \n    end --while \nend \n  \n--output the full list  \nselect dbname, vlfcount  \nfrom @vlfcounts  \norder by dbname<\/pre>\n
https:\/\/gallery.technet.microsoft.com\/scriptcenter\/SQL-Script-to-list-VLF-e6315249<\/a><\/p>\n
If your transaction log file has this problem, follow the steps below;<\/p>\n