I recently filed a suggestion on Microsoft Connect for SQL Server to change the default behavior for the FILEGROWTH argument of the CREATE DATABASE statement to work similarly to that of the SIZE argument.
According to Books Online,
“When size is not supplied for the primary file, the Database Engine uses the size of the primary file in the model database. When a secondary data file or log file is specified but size is not specified for the file, the Database Engine makes the file 1 MB. The size specified for the primary file must be at least as large as the primary file of the model database.”
“If FILEGROWTH is not specified, the default value is 1 MB for data files and 10% for log files, and the minimum value is 64 KB.”
This prevents the DBA from proactively changing the default FILEGROWTH for data files created by an application that issues CREATE DATABASE statements without specifying the FILEGROWTH argument. Worse yet, the default that it leaves you with is 1 MB for the primary data file. While there may be no such thing as the perfect default for this setting, I really struggle to see any scenario where 1 MB is ideal.
One scenario where 1 MB is absolutely not ideal and the application is frequently creating databases is SharePoint. SharePoint creates its content databases without specifying a FILEGROWTH setting. If not subsequently changed, you could have a database that grows 1 MB at a time and is primarily used to stores files (each of which might be up to 50 MB). These content databases tend to grow quite large over time as well.
Of course, there are things that a DBA can do to manage this including:
- Changing the FILEGROWTH settings as soon as the database is created.
- Growing the primary data file in the model database quite large to increase the time before the likely first autogrow.
- Monitoring file usage and manually growing files in large increments off hours before they grow automatically.
However, having some control over the default would be helpful in cases where there is no full-time DBA or an overworked DBA would like to proactively manage these settings. I believe it would be even more useful if SQL Server extended this behavior to log files as well for both the SIZE and the FILEGROWTH arguments.
If you agree, please vote at Microsoft Connect.