Trending SQL Server Tips

How to Change SQL Server Database Auto Growth Settings


Aug 02, 2012

Introduction

In this article we will go through the steps to change SQL Server Database Auto Growth Settings. It is always a best practice to set an appropriate auto growth setting for all Production database to a handle unexpected database growth which can be due to unexpected data load or due to the disk space requirements to perform maintenance tasks. The steps mentioned in this article are same across SQL Server 2005 and higher versions.

Different ways to Change SQL Server Database Auto Growth Settings

  • Change SQL Server Database Auto Growth Settings Using SQL Server Management Studio (SSMS)
  • Change SQL Server Database Auto Growth Settings Using TSQL Script

Recommendation: It is a best practice to Configuring Database Instant File Initialization Feature on windows for SQL Server to reduce the time required to growth database file.

Let us go through each of the above mentioned options in detail.

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; right click the database and select Properties from the drop down list to open up Database Properties to change the Autogrowth settings for a database as shown in the snippet below.

Check SQL Server Database Properties

3. In Database Properties; Select Files Page on the left side panel as highlighted and then click on “” button to open up Change Autogrowth for Database dialog box.

SQL Server Database Properties Dialog Box to Change Database Autogrowth Settings

4. In Change Autogrowth for Database dialog box you will see that the default File Growth Autogrowth setting is 1 MB. You can change the Autogrowth settings by changing the value either in Mega Bytes or in Percentage. However, it is better to change the value in Megabytes are this will have better control on the database file growth. The Autogrowth value should be change for both Data and Log files. In this demo I have set the data file growth as 512 MB and Log File growth as 256 MB. Once you change Autogrowth setting click OK to save the changes and return to Database Properties window.

How to Change Database Autogrowth Settings of an SQL Server Database

5. In the Database Properties Window you could see that new values for Data and Log file Autogrowth is reflected. Click OK to make the changes to the Autogrowth settings of the database.

Updated Database AutoGrowth Settings in SQL Server

How to Change SQL Server Database Auto Growth Settings Using TSQL Script

Using the below script you can change the database Autogrowth settings to grow data file at 512 MB and Log file at 256 MB.

USE [master]
GO

ALTER DATABASE [MyTechMantra]
	MODIFY FILE ( NAME = N'MyTechMantra', FILEGROWTH = 512MB )
GO

ALTER DATABASE [MyTechMantra]
	MODIFY FILE
		(NAME = N'MyTechMantra_log', FILEGROWTH = 256MB )
GO

Reference:- Considerations for the "autogrow" and "autoshrink" settings in SQL Server


Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article

Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."