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). Each of these VLFs, or chunks of the log file is marked as in use or available. If something happens, like a really huge long running transaction, or backups don’t get run for a while, the log file can grow in size.
When the log is backed up, each of the chunks or VLFs is marked as available to be reused. The log file is not made any smaller, it is the same size, just the internal VLF’s are made available for reuse.
If you want to see what is happening with the VLF’s you can check out this blog post that I wrote with a script to display the VLF’s.
If the log is staying at the current size and not growing, then it is likely not the cause of issues. If it is continuing to grow then we will want to look at it.
If something happened that caused it to bloat out in size a while back, and now it doesn’t need to be that large, then we could shrink it, but that won’t likely change anything with the current performance issues you are seeing.
Related Links
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!