Database File Auto Growth Value
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
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