T-SQL script to configure SQL Database for optimal VLF distribution

The transaction log configuration is critical for SQL database performance. SQL Server configures the default autogrowth as per the following list.

Version

Default Values

Starting with SQL Server 2016 (13.x)

Data 64 MB. Log files 64 MB

Starting with SQL Server 2005 (9.x)

Data 1 MB. Log files 10%.

Prior to SQL Server 2005 (9.x)

Data 10%. Log files 10%

The transaction log auto-growth (set by the FILEGROWTH option) must be enough to stay ahead of the database workload. The t-log growth increment should avoid frequent automatic growth.


Keeping a small growth increment for transaction log can generate too many small virtual log files. These higher numbers of logs can reduce performance.


If you keep a small growth increment for the transaction log, it can generate too many small virtual log files. These higher numbers of logs can reduce performance.

You can run the script https://github.com/rajendragp/RajendraScripts/blob/master/DatabaseVLF.sql to check databases, their log file size, used log file size, Log space used (%), Number of VLFs, Number of Free VLF and In-use VLFs.

For example, It gives the following result for my demo SQL instance



Run the following script https://github.com/rajendragp/RajendraScripts/blob/master/VLF_Fix.sql to get optimal VLF distribution for the current transaction log size of all databases in a given instance, and the required growth increments to achieve the required size.

For example, it recommends a 256 MB autogrowth value for the [DBLogger] database.


It also generates a script in the message tab.

You can copy the script from the message tab and execute it for modifying the autogrowth setting for your SQL database.

Note: The script preemptively fixes VLF issues in all SQL databases within the server, based on the transaction log current size.



26 views0 comments