Configuring Database Auto-Growth Settings In SQL Server

Database auto-growth is one of the many aspects of SQL Server that, when configured properly, performs behind the scenes and generally requires little to no modification to the way it is configured.  Auto-growth is a function of the database engine that allows a database file to grow when it is running out of available space on the server.  All database files on the server, both data and log for all available databases, have an auto-growth setting that can be configured.

When managing database auto-growth there are several basic options that are available.  Auto-growth can be configured to grow based on a certain percentage of the current database file size, a specific size (in MBs) or it can be configured to never grow.  Depending on the database or databases you are working with, any of these can be a viable option, though in my experience there are only a few scenarios where you would want to restrict auto-growth.

The task outlined below will familiarize you with the steps necessary to review and modify the auto-growth settings for the database data and log files.

The first step is to connect to the instance of SQL Server where the database you would like to review is located.  Once connected, right-click the database and select Properties.

autogrowth_database_properties

Once the Database Properties window is displayed, select the Files page on the left hand side of the window.  This will display the associated database files.  In many cases you should see a single data file and a single log file, though with more complex implementations you may see additional files.

autogrowth_database_property_window

Each file has it’s own auto-growth settings so you will want to apply the next step to each file in order to fully configure the settings for that particular database.  In the example above you will see the .mdf (primary data file) and .ldf (log data file) files for the DYNAMICS database.  They are currently configured to grow by a certain predefined size and have an unlimited growth capacity.

Once you have reviewed the associated files on the Files page click the ellipses […] next to the current Autogrowth/Max Size column open the Chang Autogrowth window.

autogrowth_change_autogrowth_window

The options here are pretty straight forward. The checkbox at the top will allow autogrowth to be enabled or disabled.  Once enabled you can configure the actual growth by either a percentage of the overall size of the database or by a standard increment in megabytes.

Additionally you can also configure the maximum file size for the data and log files or allow for unlimited growth.  Though unlimited growth is a good option, it may not always be feasible for everyone depending on the resources available.  If disk space is not a big concern, I would recommend leaving this at Unlimited.

Once you have made all desired changes click OK to close out the Change Autogrowth window.  Click OK again to close and save the changes to the Database Properties window.

The default auto-growth settings are likely not going to be the settings that are best for your database.  If you are familiar with the growth profile of the database when you first create it then you should configure the auto-growth settings based on that profile.  However, if you do not know how fast the database will grow then it’s best to keep a close eye on database auto-growth events until you have a good understanding and can configure the auto-growth settings to manage this.