Quick Scan Report – High VLF Count – Video Tip

Virtual Log Files (VLFs) are part of the SQL Server log file. When space is allocated in the log due to growth, that new chunk of log is broken up into Virtual Log Files. High VLF counts can slow down the following actions:

– Transaction Log Backup

– Crash Recovery, the process your database goes through on startup.

– Writing to the transaction log.

Transcription:

Steve Stedman 0:09
Welcome to today’s training video. I’m Steve Stedman. I’m the founder of Stedman Solutions. Today we’re going to talk about one of the common items reported by the sequel daily checkup. And the SQL daily checkup is a product that we make available to monitor and keep track of what’s going on with your SQL Server. And we’re going to cover one of the items and what it means and what we can do to resolve it. All right, the SQL daily checkup from Steadman solutions. This is a product that we make that we install on your server that monitors and lets us know when there’s issues. And it lets you know with alerting and notification what some of those issues are. Let me show you an example. Here’s an example of the output of one of those reports. And this is just on one of my test servers. But you would get an email with different notice of where the problem areas are. Some of the reports have specific issues, like you’re showing check DB not run recently. But today, we’re just going to take a look at one of those issues and what we can do to resolve it. The high virtual log count warning that comes out from the daily monitoring is one of those things that can be really important. What this means is that the database that we’re monitoring has greater than several 100 virtual log files. So first off, what is a virtual log file? Well, when SQL Server creates a log file on, it might be a couple of megabytes, or it might be a couple gigabytes, or many gigabytes. And inside of that log file are different chunks of the file that can be used to store different amounts of log data. And if you initially create the file, in small chunks, it grows with each of these usable chunks as being very small. The problem you run into is if you get too many of these, you end up in a position where it’s really slow to do database restores. In fact, it can be so slow that

it can add hours so the time it takes to restore your database if your VLF count is too high. An example was I worked with a customer on a restore of a database where it was taking over eight hours to do the Restore. Afterwards, I tested it and confirm that that database had over 150,000 virtual log files. And the majority of that time was taken to restore and rebuild the log file. We reduce the number of VLF in the log file and attempted to restore again, and it restored in just about an hour. So many hours of time was spent specifically on a delayed restore. So if you want to look at on how to see what your VLF files look like inside of your log, you can look at SteveStedman.com/vlf. In there, there’ll be a article called visualizing VLF. Another update. And when we click on that, there’s some sample code here. And then this item, see how it says only on SQL Server 2012. And newer, if you’re running this on a previous version of SQL Server 2000, or before 2012, comment out this one line, and everything else will work the same. But what this does is it looks at your log and shows you what your virtual log file looks like. So let’s take a look at how that code works with an actual database. Here’s a test database that I set up, I simply call it VLF test. And what I did is I set some of the settings to cause some problem. But this is one of those things that you could do. It could happen on an actual database, I did it this way by I set the log file growth to grow in one megabyte chunks that’s really small. Normally you do much larger than that. But the same thing could happen, where if you’re doing 100 megabytes, or even 10 megabytes or a percentage, you can end up with lots of log files. But by setting this to one megabyte that caused a problematic situation where the log file grew too fast. So what I did is I just created a single table in this database with a couple of goods as values and then a VAR char column there was some data and it just taken up some space. And then I inserted a few 100,000 rows into it and did some updates and basically did a bunch of things that caused the log file to grow. And then I created an index and did some more updates and some inserts. And then if we take a look at this script from my website, where we look visualize the VLF. What this does, is it kind of draws a bar chart down here below with zeros and X’s showing what’s in use and what’s not used. And based off of how this little database that I created, grew by inserting all those root rows that grew in one megabyte chunks. And if we look at the number of rows, we scroll down, it’s just over 1001 virtual log files inside of that, that actual server log file. Now one of the things that can cause your log file to grow and create lots of chunks like this is if you don’t back up for a while, if your backup processes broken, and your log just continues to grow, and then you start running backups, again, your log might be bloated. Now, generally, I like to see this number of rows and the few 100. Now, maybe four or five 600 isn’t a big deal. But anytime you start getting over 1000, or more than 10,000, or even in the 100,000 range for the number of VLF, or virtual log files, that can really bog things down, it can really slow things down. So what we recommend doing when it gets to this point is first backing up your database and clearing out the log, but that doesn’t delete the files, it just deletes what’s in use. And then, if your log file needs to be the size it is we recommend shrinking the log file, growing it out again to the size it needs to be but growing it in bigger chunks rather than in smaller chunks. And that will make it so that you have a big enough log file for what you need to manage, but not as many virtual log file chunks or pieces inside of your log file. So as it says here, it’s recommended that this can cause problem and performance. You can do this without any downtime, however, you want to do it at a time where the server is not being heavily used. And what you do is you backup the database, you then shrink the log file down to something where there’s a lower number of VLF ‘s and then you expand the log file out in bigger chunks and get it to a point where the VLF files count is not too high. Here’s some more information that may help you out my blog. We’re gonna get more information. SteveStedman.com or StedmanSolutions.com. The last link will be the Stedman Solutions, a sequel, daily checkup, monthly subscription. We have lots of people using the sequel daily checkup today and if it’s something that you’d like to use, let me know and we’ll get it set up for you. Alright, have a great day.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!