How to change SQL Server Auto Growth Settings

Introduction

This topic explains the process used to change SQL Server Database Auto Growth Settings.

It is best practice to set an appropriate auto growth setting for databases to handle unexpected growth which can be due to unexpected data load or disk space limitations.

 

How to Change SQL Server Database Auto Growth Settings Using SQL Server Management Studio (SSMS)

  1. Connect to SQL Server Instance Using SQL Server Management Studio.

  2. Expand Databases then right-click on the database that requires the changes.

  3. From the drop-down list, select Properties to open up the Database Properties screen for the database selected.

 

The following screen displays.

  1. From the Select a page section, select Files to highlight and then click on the '…' button.

 

The following screen displays.

  1. The default File Growth setting is 50 MB. This can be changed either in Mega Bytes or in Percentage.

  2. It is advised to change the value in Megabytes as this will better control the database growth.

  1. The Autogrowth value should be changed for both Data and Log files.

  2. Click OK to save and return to the Database Properties screen.

 

In the example below, the Data File growth is 512 MB and the Log File growth can be 256 MB.

  1. Click OK to make the changes to the Autogrowth settings of the database.