Database File Auto Growth Inadequate

Database free space should be monitored and maintained but, should the worst happen, having autogrowth enabled with a practical value will help to improve the overall performance of the instance and reduce fragmentation.

Suggested Action

šŸ’” Aireforge Advisor can automatically generate a script for this.

Enable autogrowth and configure the growth to the recommended value. The default values are inadequate for most databases and can cause performance issues. Therefore we recommend the following settings:

Database Size Warning Size Recommended Size
<256MB 64MB 64MB
>256MB <5% 10%

Also consider implementing instant file initialization to limit the impact of unexpected autogrowth events.

How to Specify Auto Growth Values for a SQL Server Database

ALTER DATABASE [WhippetWorks]
MODIFY FILE
(
    NAME = N'WhippetWorks',
    FILEGROWTH = 10%
)
GO

ALTER DATABASE [WhippetWorks]
MODIFY FILE
(
    NAME = N'WhippetWorks',
    FILEGROWTH = 64MB
)
GO

Further Reading

Considerations for the "autogrow" and "autoshrink" settings in SQL Server | Microsoft Support

SQL Server Database Growth and Autogrowth Settings | Simple Talk

Database Instant File Initialization | Microsoft Docs