1.877.833.1202

SQL server file growth tuning

For each of the four databases, the initial size and growth rate of the two* related database files should be configured. It is recommended to set a growth rate of the data and log files to be a specific size instead of a percentage growth rate.

When SQL expands the data file, it is usually a quick process as it does not have to write zeros to the drive to grow the space; however, for a log file, SQL has to clean the data. It expands the disk space by writing zeros to the drive. This operation can take time and if it happens at the same time a user performing a non-trivial query they may receive a timeout.

A good starting point for growth is to use 256 MB for the database file and 64 MB for the log file.

SQL file growth

* If simple recovery mode is enabled for the database there is only one file.