dbatools is a free PowerShell module with over 180 SQL Server administration, best practice and migration commands included.


Expand-SqlTLogResponsibly

This command will help you to automatically grow your T-Log database file in a responsible way, preventing generation of too many VLFs.

Too many virtual log files can cause transaction log backups to slow down and can also slow down database recovery, and in extreme cases, even affect insert/update/delete performance.

In order to get rid of this fragmentation we need to growth the file taking the following considerations:

  1. How many VLFs are created when we do a grow or when auto-grows hits
  2. Consider the different SQL Server algorithms for autogrowth
  3. Consider known bugs when determining growth size

Currently, this script does not analyse the actual number of VLFs (use DBCC LOGINFO) or backup/shrink your database prior to growing the transaction log. You must take these actions prior to running this script otherwise only half of the correct process will be made. Due to the sensitivity of automating this process, automation is not available in the first release.

Examples

To properly grow the T-Log of ‘Test’ database on sqlsvr instance to 50000 MB.

Expand-SqlTLogResponsibly -SqlServer sqlsvr -Databases Test -TargetLogSizeMB 50000

To properly grow the T-Log with FielId 9 of database db1 on localhost instance to 1GB.

Expand-SqlTLogResponsibly -SqlServer . -Databases db1 -TargetLogSizeMB 1024 -LogFileId 9

References

Transaction Log VLFs – too many or too few?
Too Many Virtual Log Files (VLFs) Can Cause Slow Database Recovery
Transaction log file size will not grow exactly 4GB when filegrowth=4GB

Author

This command was created by Cláudio Silva. You can find Cláudio on Twitter and LinkedIn.

Get-Help

From PowerShell, execute Get-Help Expand-SqlTLogResponsibly -Detailed for more information on this function.

Source Code

Want to see the source code? View Expand-SqlTLogResponsibly.ps1 on GitHub
 

Related commands