Quick Scan Report – Percent Autogrowth Enabled

The Quick Scan Reports display a Warning if you have percent growth enabled on any database or log files.

You can set your database file to grow by a percentage or a specific size in megabytes.

For small databases the setting doesn’t matter much since the fact that they are small implies that they haven’t had much growth. For larger databases this the wrong setting can be an issue, if you have a 3TB database that needs just a bit more room to add a couple rows if your autogrowth is set to a percentage, like 10%,then you will end up allocating 300gb of file space to insert just a couple of rows. In this example time it takes to allocate the 300gb will be substantial, and there will be blocking occurring while that space is being allocated.

This problem is even worse when it applies to log files. If the log continues to grow with a percentage growth this will cause a larger number of VLFs (Virtual Log Files) inside of the log file, which impacts performance.

Recommendation: determine the proper size for your database files, and find an auto growth setting (not a percentage) that is appropriate for your environment.

You can get to the file growth settings by right clicking on a database in SSMS, choosing properties, then click the Files tab, and click the “….” button in the Autogrowth/ MaxSize column.

Here is an example of the wrong way to do it with percent autogrowth enabled.

And a better option to grow the file by a specific size in megabytes.

Quick Scan Report – Percent Autogrowth Enabled

See Also: