SQL Server Articles, Tips, FAQs, Reviews, Whitepaper, News etc - MyTechMantra.com

How to Change SQL Server Database Auto Growth Settings

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

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.

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

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.

Change AutoGrowth for SQL Server Database from its Default Settings
Change AutoGrowth for SQL Server Database from its Default Settings

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.

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

Conclusion

DBA’s shouldn’t rely on the default SQL Server Data and Log file Autogrowth value and must always set it to more realistic values depending upon the usage of the database.

Chetna Bhalla

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Add comment

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us...

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Recent SQL Server Tips