How to Change SQL Server Database Auto Growth Settings

Ref:
http://www.mytechmantra.com/LearnSQLServer/How-to-Change-SQL-Server-Database-Auto-Growth-Settings.html

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

MyTechMantra 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.

 

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.

 

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.

 

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

To get Database size in MB for SQL

Weekly Server Report for SQL

T-SQL:


IF OBJECT_ID('tempdb..#T','U') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T (DBName nvarchar(500),SizeinMB nvarchar(100),FreeSpaceinMB nvarchar(100))
GO

INSERT INTO #T 
EXEC sp_MSforeachdb 'USE ? SELECT ''?'' DBName,
sum(size/128.0) [Size in MB],sum(size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0)[Free Space in MB]
FROM sys.master_files
where DB_NAME(database_id)=db_name()
group by DB_NAME(database_id)'
GO 

SELECT * FROM #T ORDER BY DBName