sp_Blitz Result: High Virtual Log File (VLF) Count
VLF stands for Virtual Log File and the transaction log file is made up of one or more virtual log files. The number of virtual log files can grow based on the autogrowth settings for the log file and how often the active transactions are written to disk. Too many virtual log files can cause transaction log backups to slow down and can also slow down database recovery, and in extreme cases, even affect insert/update/delete performance.
This part of our SQL Server sp_Blitz script checks the number of virtual log files (VLFs) in each database and alerts when there’s 1,000 or more. Right at 1,000 may not be a problem based on your database size – we just want to bring the problem up before it becomes critical, since fixing this issue is a pain in the rear.
To Fix the Problem
Bad news: we’re going to have some downtime. The whole server doesn’t have to be down, but to fix this, we have to shrink and regrow the log file – and that’s a blocking operation. To learn how:
- Watch Jes Schultz Borland’s 30 minute video: How SQL Server Works: Log File
- Check out Dave Levy’s post on A Busy/Accidental DBA’s Guide to Managing VLFs
Learning More About SQL Server and Storage
Want to learn more? We’ve got video training. Our VMware, SANs, and Hardware for SQL Server DBAs Training Video is a 5-hour training video series explaining how to buy the right hardware, configure it, and set up SQL Server for the best performance and reliability. Here’s a preview: