What is Vlf(Virtual Log File)?
Vlf (Virtual Log File) is a virtual file, as the name implies.
The Transaction Log file with the ldf extension in SQL Server consists of one or more virtual log files.
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 many and will also cause performance bottlenecks.
High virtual log file (vlf) count
It increases the recovery time of the database.
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.
High number of vlf will also increase the log backup time. In some cases it also increases insert / update / delete times.
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.
Find VLF Count
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.
You can use Kevin Riley’s script to check which databases on instance have this problem.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 |
--variables to hold each 'iteration' declare @query varchar(100) declare @dbname sysname declare @vlfs int --table variable used to 'loop' over databases declare @databases table (dbname sysname) insert into @databases --only choose online databases select name from sys.databases where state = 0 --table variable to hold results declare @vlfcounts table (dbname sysname, vlfcount int) --table variable to capture DBCC loginfo output --changes in the output of DBCC loginfo from SQL2012 mean we have to determine the version declare @MajorVersion tinyint set @MajorVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))-1) if @MajorVersion < 11 -- pre-SQL2012 begin declare @dbccloginfo table ( fileid smallint, file_size bigint, start_offset bigint, fseqno int, [status] tinyint, parity tinyint, create_lsn numeric(25,0) ) while exists(select top 1 dbname from @databases) begin set @dbname = (select top 1 dbname from @databases) set @query = 'dbcc loginfo (' + '''' + @dbname + ''') ' insert into @dbccloginfo exec (@query) set @vlfs = @@rowcount insert @vlfcounts values(@dbname, @vlfs) delete from @databases where dbname = @dbname end --while end else begin declare @dbccloginfo2012 table ( RecoveryUnitId int, fileid smallint, file_size bigint, start_offset bigint, fseqno int, [status] tinyint, parity tinyint, create_lsn numeric(25,0) ) while exists(select top 1 dbname from @databases) begin set @dbname = (select top 1 dbname from @databases) set @query = 'dbcc loginfo (' + '''' + @dbname + ''') ' insert into @dbccloginfo2012 exec (@query) set @vlfs = @@rowcount insert @vlfcounts values(@dbname, @vlfs) delete from @databases where dbname = @dbname end --while end --output the full list select dbname, vlfcount from @vlfcounts order by dbname |
https://gallery.technet.microsoft.com/scriptcenter/SQL-Script-to-list-VLF-e6315249
If your transaction log file has this problem, follow the steps below;
- Backup the Transaction Log
- Shrink the Transaction Log
- Set Acceptable Initial Size(eg 1024 mb)
- Set Acceptable Auto Growth(eg 512 mb)
To shrink the Transaction Log file, you can read the article titled “How To Shrink SQL Server Transaction Log“.
