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 …
I often times get the question similar to “Why is my log file not shrinking when I do regular backups”? It goes to show there the functionality of the SQL Server transaction log is not entirely clear. The way that backups and the log file work is that the log file is made up of internal virtual log files (VLFs). …
Why is my log file not shrinking when I do regular backups? Read more »
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 …
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. Here is what people have to say about Database Health Monitor. Excellent – It’s the first tool I open every morning …
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 …
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 …
DBCC ShrinkDatabase – I want to shrink my database. Read more »
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 …