|sp_dbfiles_autogrow - adjust autogrow values for database files|
About me and Nucleus
Articles, tips etc.
Courses and training
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.
SQL Server 2008 and later. See comment block in procedure source code for version history of the procedure.
Whether to output the table created in the proc, which has the value recommendations.
Whether to output an overview of the current situation.
If we want to outout commands only where we deviate from the recommendations.
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.
You find the code here.