Visualizing Log File VLF Sizing
One of my favorite queries this week is the following query that creates a text based bar chart to quickly help visualize the VLF files on any database log file. In the last month I have given the “TempDB Do This and Don’t Do That” presentation twice, once at the Bellingham SQL Server users group, and another time at SQL Saturday Redmond. One of the questions that came up after the users group meeting was around an easy way to visualize the Virtual Log Files. Its one thing to just get a count, but to see the sizing of each VLF is helpful to understand how VLFs work.
If you are not familiar with VLFs, these are Virtual Log Files, or the chunks that make up your entire log file. When your log file grows, VLFs are added to help manage the file space. For the log growth, between 4 and 16 VLFs are added for each growth of the log file (except on SQL Server 2014 and newer, which modifies the sizing algorithm a bit).