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.