Skip to content

VLF

Visualizing VLFs – Another update

This is an update to Visualizing VLFs – A while back a wrote a blog post with a query to Visualize the VLF’s in your database. Today I have an update to that script.

The previously updated script added another column called “TextStatus” to the output to give you a better idea of what the different statuses mean. You now get 3 statuses shown, “In Use”, “Available”, and “Available Never Used”. The If you have lots of VLFs that are “Available Never Used” that may be an indication that your log file may be larger than you need. If you don’t have any that are “Available Never Used” the log may be smaller than you need.

This latest update changed the character shown in the bar chart to a X or an O depending on if the current file is in use.

Read More »Visualizing VLFs – Another update

Database Health Monitor Version 2.4 Released

If you haven’t tried Database Health Monitor Version 2.4 might be a good time to try it out. Five and a half years of my development time has gone into Database Health monitor, and hopefully the features will show it.

Star_of_life

Here is what people have to say about Database Health Monitor.

Excellent – It’s the first tool I open every morning to see what is going on in my production environment! Thanks for the great work!

I personally used Database Health Monitor to manage over 100 instances of SQL Server, and it speeds up many of the daily maintenance checks.

Version 2.4 Release Notes

Over the last several months I have added several new features, and many bug fixes. Here are some of the features and fixes included in this update.

Read More »Database Health Monitor Version 2.4 Released

How Many VLFs is Too Many?

Here is an error that popped up in the SQL Server error log today:

5/6/2016 10:10:10 AM spid22s Database [DatabaseName] has more than 10000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

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

After looking further into this server, the VLF count turned out to be around 163,000. That is certainly the highest VLF that I have ever seen.

My personal threshold for too many VLFs is usually around 200 with my preference to be less than 100 VLF files. Others may have their preferences, they will all be far less than 10,000, and certainly less than the 163,000 that I had the opportunity to see today.

Read More »How Many VLFs is Too Many?

DBCC ShrinkDatabase – I want to shrink my database.

TL;DR summary: Don’t do it. Stop reading here if you want, but just don’t do it.

This post refers to shrinking your database files (mdf, or ndf files), not shrinking the log file. The log file is a completely different conversation, however shrink database does shrink the log file.

Not shrinking your database is one of the more counter intuitive things out there. You might think that a smaller database is a good thing, however there are some negative side effect if you shrink your database regularly, or have the autoshrink option enabled. Side effects of shrinking your database include:

  • Excessive I/O due to the shrink.
  • Index fragmentation (most likely all of your indexes).
  • Excessive I/O to defragment your indexes.
  • After the shrink is complete, inserting or updating rows that require more space in your database will be slowed due to the time involved with growing your data file.

Read More »DBCC ShrinkDatabase – I want to shrink my database.

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).

Read More »Visualizing Log File VLF Sizing