Last week, I took on a project to fix the transaction log size and virtual log files (VLFs). Our transaction log had grown to the same size as the database file. This isn’t necessarily a bad thing, but I knew that it was wasted space: the log didn’t need to be that large on a regular basis. Because of the size of the log, I figured that there would be some issue with its underlying VLFs as well. VLFs are the micro-segments that compose the overall transaction log. But why would anyone want to “fix” VLFs? As Kimberly Tripp ( Blog | @KimberlyLTripp ) explains in point number eight in her 8 Steps to better Transaction Log throughput, if the transaction log hasn’t been pre-allocated properly or if it has been allowed to autogrow purposelessly, it will likely be internally fragmented with too many VLFs of improper size.
What does it even mean? Take a look at the visualization: the transaction log is cyclical in nature. When it fills up and reaches the end of the log file, it will loop back to the beginning and overwrite inactive VLFs. If those VLFs haven’t been inactivated via log backup and the end of the log meets the beginning of the log (I.E. the transaction log has filled completely), then the log will attempt to grow itself and add additional VLFs. (This assumes, of course, that the file allows for growth. If it is not set for growth, then you will receive a 9002 error, “The log file for database is full.”)
Now assume that the autogrowth settings haven’t been set properly – or possibly even worse, have been left on default – and the log has to grow multiple times in succession… all this activity can create a mess of new VLFs and fragment the log file. Some of the repercussions of this are slower transaction log throughput and slower database startup, which isn’t a big deal to me, and slower transaction log throughput, which can be a major pain point even on non-mission critical systems.
As I mentioned, I decided to take a closer look at the transaction log. The file itself had grown to the size of the database file. I also figured something might be amiss with the VLFs as well because of the size. I knew that no one had kept track of these kinds of details before me and that the database had been mismanaged in the past – hence the overly large transaction log file.
As Kimberly mentions, the number of VLFs in the log file are equal to the number of rows returned by the DBCC LOGINFO command. Running that command, I returned 500 rows, which is ten times the warning number that Kimberly quotes. (Not that we should follow numbers just to follow numbers, but 500 VLFs is a massive number for a 6GB log file and is indicative of improper sizing and maintenance.)
I began by running through Kimberly’s procedure on my test box to familiarize myself with the process. Successful on test, I moved to production. Success again: I shrunk from 500 VLFs to 18.
Interestingly, in both instances I had to repeat the first two steps of the procedure – backing up the log, and then using DBCC SHRINKFILE(name, TRUNCATEONLY) – before I could use ALTER DATABASE to modify the log file to an appropriate size. As explained in BOL’s Shrinking the Transaction Log, the logical log (AKA the cyclical, VLF portion) must be deactivated at the end of the physical log before the file can be shrunk.
In my case, I ran my first LOG BACKUP to deactivate a handful VLFs. When I executed the first DBCC SHRINKFILE, some VLFs were truncated (from 500 to 465) and the logical log was wrapped around to the beginning of the file. I was also given a message that I should run a log backup to free up more portions of the log. So I did, thereby deactivating more VLFs. That’s when the second DBCC SHRINKFILE dropped the remaining VLFs from 465 to 18. At that point, I executed ALTER DATABASE to resize the transaction log file. I shrunk it to 512MB because I noticed that the log backups grew to around that size during overnight processing.
Fixed for Good (for now)
Now it’s simply not enough to fix the VLF issue and resize the log file. It’s likely that the situation will return if additional actions aren’t taken to adjust the logging behavior of SQL Server.
First, I modified the autogrowth rate of the log file. I set the file to autogrow by 512MB each time. I figured that it would probably grow once near immediately, but since the VLFs had been resized appropriately I wouldn’t see additional growth with the current level of transaction processing. I was correct in my assumptions: the log file grew to 1GB that same day to a total of 26 VLFs. The log has yet to grow again.
Second, I modified my transaction log backup plan. Before I took over this database, it had been set to backup every four hours. I wasn’t comfortable with that level of risk, and so I reduced it to every two hours. I now know that I should have been more prudent, and I believe that more frequent log backups would have helped to prevent some of the massive growth. So I further reduced that time period to 30 minutes. Again, I haven’t experienced log growth or VLF issues since adjusting this timing.
Cold Hard Facts
During this small project, I had also been working on some data imports. I’ve written in the past about our poor transaction performance many times, and I knew that this latest import might exacerbate our log problems if I did nothing to prevent it. During my pre-fixed-log tests, my imports were taking an average 20 minutes to complete. Post-log goodness, this transaction time was cut to the four to five minute mark. By taking a little time to work on and adjust the logging strategy, I had saved roughly 75% of my own time! Three-and-a-half hours that I’d simply be waiting for inserts to complete.
Room for Growth
In this case, I am not talking about transaction log growth. I believe that I could further improve our transaction log with better metrics. I don’t think that it needs to be 1GB in size. (Thankfully I don’t have space issues to press that point.) I also think that by rewriting a few of our transaction heavy stored procedures, and by taking even more frequent log backups, I could ensure that the log file stayed at the 512MB mark. But as with all projects, sometimes the immediate solution is good for the time being. Still, if this problem rears its head again, I am prepared to tackle it and know that I can take the solution much further.
Update – Be sure to read this follow-up post where I document why ALTER DATABASE is necessary after using SHRINKFILE.