Our department has had some concerns originating from high VLF counts. We have a few DBs with counts of 5,000.
I've read that high VLF counts can slow down log backups and restore operations. I don't understand how that is possible? My understanding is that VLF counts are like a fragmented Log File. When tables are fragmented, they can affect performance, but usually very little. So the log file has fragments, how is this a big deal? Will this also cause full backups to be slower or just log backups?
Anyways, I'm trying some timing experiments with full backups while shrinking the log file and reducing the VLF count
I've encountered slow restore operations due to many VLFs. There's a very good writeup on getting detailed backup/restore data via extended events:
I've used this (or a variation of it) to find out what happens after the restore hits "100 percent finished"....and just sits there for several minutes. There are VLF operations that continue after that point, those are highlighted in the images in that article. If you have thousands of VLFs they can add considerable delay.
It's easy to test the impact: make backups of the database and log when the VLF count is high, then reduce the VLFs and make new backups. Restore all of them while capturing the extended events and compare. I remember trimming something like 30 minutes or more off a restore operation by reducing the VLF count down to 100 or so.
Full backups can be impacted if there's a lot of write activity in the database after the backup starts, as it will include the active part of the transaction log in the full backup.
Edit: another thing about high VLF count, they can make your backup files larger even if there's no log activity. I remember having a log with 50K or so VLFs, completely empty, log backup was 128 MB or something. Getting it down to 100 VLFs made the backup 128 KB.
2 Likes