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:
- How many VLFs are created when we do a grow or when auto-grows hits
- Consider the different SQL Server algorithms for autogrowth
- 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.
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