This is the second in a series of posts on using the DBATools suite of PowerShell functions to administer SQL Server environments. In my first post, I showed how to restore an Availability Group database. In this post, I demonstrate how to use the DBATools functions to resize a transaction log file and reduce the number of virtual log files (VLF) it contains.
All changes to a SQL Server database are executed within transactions. All transactions begin and commit on completion. A transaction that has begun but not committed is described as open. Each change made to the database is written to the transaction log file. The log file is written to sequentially and is divided into logical units called virtual log files.
On initialization, the log file contains one active VLF. The rest of the VLFs in the file are free. As log records are written to a VLF, it is marked as active. SQL Server will continue to write to the transaction log file until it reaches the physical end of the file. At this point, it checks to log file for free VLFs. If a free VLF is found, it continues writing to that VLF. If not, the log file grows, adding additional VLFs. For a database in FULL recovery, VLFs are marked free when a log backup runs if no log records in that VLF are needed. Log records are needed if they are part of an open transaction, have not been included in a log backup, or are required for high availability (Availability Groups, mirroring, log shipping).
Recommended best practice is to maintain VLF counts between 500 and 2000. The script below checks the instance for VLF counts that exceed the limit passed in. It can return a list of log files that exceed the limit or can resize those file to reduce the VLF count. It does this using the DBATools function Expand-DbaDbLogFile which performs the following steps:
- Runs a transaction log backup to the default backup path, unless a backup path is provided.
- Shrinks the transaction log file to the specified shrink size to reduce the VLF counts.
- Grows the log file to the current size or to the size specified. If the desired size is greater than 8192MB, the file will be grown in increments of 8192MB.
More information about the DBATools suite of PowerShell functions can be found here.
ResizeLogFilesFunction.txt