Maintaining the number of virtual log files (VLFs) in a transaction log is a task that is routinely performed in analysis of SQL Server instances. Numerous blog post have been dedicated to covering the issue of maintaining an efficient number of VLFs. Kimberly wrote about the impact of too few or too many files and Paul wrote about how Microsoft changed the algorithm in SQL Server 2014 for how many VLFs are created when a log file is grown. Way back in 2005 Kimberly shared 8 steps to better transaction log throughput. This post is where Kimberly told us how to reset the VLFs in a transaction log by backing up the log, shrinking the log, and then likely having to repeat the process several more times.
Since Azure Managed Instance manages the backups for you, are you still able to manage VLF fragmentation? Let’s find out.
I connected to my managed instance and created a test database. Next I created a table with three columns and made them each a uniqueidentifer.
CREATE TABLE [dbo].[IDTABLE](
[ID1] [uniqueidentifier] NULL,
[ID2] [uniqueidentifier] NULL,
[ID3] [uniqueidentifier] NULL
) ON [PRIMARY]
I modified the database log file to auto grow at 1 MB instead of the default 16 MB and then ran DBCC LOGINFO() to see that I only have 4 VLFs.
I inserted 150k records to cause the database and log file to grow by inserting NEWID into each of the three columns.
INSERT INTO [dbo].[IDTABLE]
(NEWID(), NEWID(), NEWID())
I checked for the VLF count again and had 247 VLFs. While 247 VLFs may not produce any noticeable performance impact, the size of the log file was proportionally small for 200+ VLFs. In this exercise, the log file grew to approx 250 MB.
How many VLFs are too many? I’m personally not concerned until I start seeing 1000+, unless the log file is small. In this case, with the log file in MB being close to the number of VLFs, I would recommend resetting the VLFs and then manually grow the log file to 256 MB or possibly 512 MB. I would also recommend increasing the auto grow value from the 1 MB I configured to 64 MB to 128 MB. Keep in mind, if it had grown to 250 MB during normal operation, these would be decent values. If the log file was 20 GB, I would recommend a larger value.
The known method to reset VLFs in SQL Server is to backup the log, shrink the log file and then repeat a few more times, if needed. In Azure Managed Instance, backups are handled for you. You can make COPY_ONLY backups, however that will not have the same effect.
So how can we reset VLFs? I decided to just try and shrink the log file since I know log backups are happening automatically. I did this by running DBCC SHRINKFILE (2,1)
The VLFs were reduced to 29.
I waited for a short period (5 or so minutes) to allow an additional log backup to occur and ran DBCC SHRINKFILE (2,1) again. This time the VLFs were reduced to 8.
At this point, I could have waited a little longer and try to shrink again to reduce to a smaller number, or I could manually grow the log file to make sure I have a good balance of VLFs to the size of the file.
For brand new databases, setting a proper size auto grow setting can help minimize having too many VLF files. At the same time, if you have an idea of how big the log file should be, you can follow Kimberly’s guidance by growing the file by 4 GB or 8 GB increments to get a good balance of VLFs across your transaction log.