Nucleus Datakonsult sp_dbfiles_autogrow - adjust autogrow values for database files


About me and Nucleus

Articles, tips etc.
My blog
Microsoft articles

Courses and training

Microsoft Most Valuable Professional


Many databases were created with default, or for other reasons, too small size for autogrow. This leads to frequent autogrow, which can be bad for sevaral reasons. We recommand that you set a "reasonable" autogrow value. This procedure was created to assist and semi-automate that work. It reads the current autogrow values and based on a table variable propulated in the procedure generates an ALTER command to adjust the value.

Versions etc.
SQL Server 2008 and later. See comment block in procedure source code for version history of the procedure.

@output_size_recommendations char(1) = 'n'
Whether to output the table created in the proc, which has the value recommendations.

@output_overview char(1) = 'n'
Whether to output an overview of the current situation.

@deviants_only char(1) = 'y'
If we want to outout commands only where we deviate from the recommendations.

@only_if_smaller char(1) = 'y'
If we want to generate commands only where current size is smaller than the recommended size.

How to use it
The procedure is on the conservative side. This means that it generates rather small autogrow settings. If you want to adjust the values generated, then just modify the insert for the table variable @rec in the beginning of the procedure code.

Just execute the procedure and check the "Messages" windows for the generated commands.

See comment block in procedure source code for version history of the procedure.

Usage examples
EXEC sp_dbfiles_autogrow

EXEC sp_dbfiles_autogrow 'y', 'y', 'n', 'n'

EXEC sp_dbfiles_autogrow
@output_size_recommendations = 'n'
,@output_overview = 'n'
,@deviants_only = 'y'
,@only_if_smaller = 'y'

The code:
You find the code here.