Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Script to change auto growth settings for all databases in SQL Server

By Shashank Srivastava, 2015/05/11

This script is going to change the auto growth setting of database files of all databases except Master & Msdb by 100 MB. This script is very useful while performing the SQL RAP exercise.

If you want to change the auto growth size from 100 to some other value then just modify the filegrowth value in script at below location.

BEGIN

PRINT 'Changing AutoGrowth option for database:- '+ UPPER(@name)

SET @Query = 'ALTER DATABASE '+ @name +' MODIFY FILE (NAME = '+@vFileName+',FILEGROWTH = 100MB)'

EXECUTE(@Query) BEGIN

PRINT 'Changing AutoGrowth option for database:- '+ UPPER(@name)

SET @Query = 'ALTER DATABASE '+ @name +' MODIFY FILE (NAME = '+@vFileName+',FILEGROWTH = 100MB)'

EXECUTE(@Query)

I am hopeful that this script is going to be very helpful to each one of you.

IF EXISTS(SELECT name FROM sys.sysobjects WHERE name = N'ConfigAutoGrowth' AND xtype='U')  
    DROP TABLE ConfigAutoGrowth  
GO    
CREATE TABLE DBO.ConfigAutoGrowth  
(  
iDBID       INT,  
sDBName     SYSNAME,  
vFileName   VARCHAR(max),  
vGrowthOption   VARCHAR(12)  
)  
PRINT 'Table ConfigAutoGrowth Created'  
GO  
-- Inserting data into staging table  
INSERT INTO DBO.ConfigAutoGrowth  
SELECT   
    SD.database_id,   
    SD.name,  
    SF.name,  
    --sf.fileid,   
    --SUSER_NAME(owner_sid),  
    --recovery_model_desc,  
    CASE SF.status & 0x100000  
    WHEN 1048576 THEN 'Percentage'  
    WHEN 0 THEN 'MB'  
    END AS 'GROWTH Option'  
FROM SYS.SYSALTFILES SF  
JOIN   
SYS.DATABASES SD  
ON   
SD.database_id = SF.dbid  
GO  
  
-- Dynamically alters the file to set auto growth option to fixed mb   
DECLARE @name VARCHAR ( max ) -- Database Name  
DECLARE @dbid INT -- DBID  
DECLARE @vFileName VARCHAR ( max ) -- Logical file name  
DECLARE @vGrowthOption VARCHAR ( max ) -- Growth option  
DECLARE @Query VARCHAR(max) -- Variable to store dynamic sql  
  
  
DECLARE db_cursor CURSOR FOR  
SELECT   
idbid,sdbname,vfilename,vgrowthoption  
FROM configautogrowth  
WHERE sdbname NOT IN ( 'master' ,'msdb' )   
--AND vGrowthOption  = 'Percentage' or 'Mb' 
  
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbid,@name,@vfilename,@vgrowthoption    
WHILE @@FETCH_STATUS = 0  
BEGIN  
    PRINT 'Changing AutoGrowth option for database:- '+ UPPER(@name)  
    SET @Query  = 'ALTER DATABASE '+ @name +' MODIFY FILE (NAME = '+@vFileName+',FILEGROWTH = 100MB)'  
    EXECUTE(@Query)  
  
FETCH NEXT FROM db_cursor INTO @dbid,@name,@vfilename,@vgrowthoption    
END  
CLOSE db_cursor -- Closing the curson  
DEALLOCATE db_cursor  -- deallocating the cursor  
  
GO  
-- Querying system views to see if the changes are applied  
  DECLARE @SQL VARCHAR(8000), @sname VARCHAR(3)
SET @SQL=' USE ?
SELECT ''?'' [Dbname]
,[name] [Filename]
,CASE is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR(5),growth)+''%''
ELSE CONVERT(VARCHAR(20),(growth/128))+'' MB''
END [Autogrow_Value]
,CASE max_size
WHEN -1 THEN CASE growth
WHEN 0 THEN CONVERT(VARCHAR(30),''Restricted'')
ELSE CONVERT(VARCHAR(30),''Unlimited'') END
ELSE CONVERT(VARCHAR(25),max_size/128)
END [Max_Size]
FROM ?.sys.database_files'

IF EXISTS(SELECT 1 FROM tempdb..sysobjects WHERE name='##Fdetails')
DROP TABLE ##Fdetails
CREATE TABLE  ##Fdetails (Dbname VARCHAR(50),Filename VARCHAR(50),Autogrow_Value VARCHAR(15),Max_Size VARCHAR(30))
INSERT INTO ##Fdetails
EXEC sp_msforeachdb @SQL
SELECT * FROM ##Fdetails  ORDER BY Dbname
  
--Dropping the staging table  
DROP TABLE ConfigAutoGrowth   
GO

Total article views: 4331 | Views in the last 30 days: 5

FORUM

Determining Filegrowth for a completely new database

2007/12/12

How can i determine (by calculations or other means ) the percentage of filegrowth for a newly creat...

BLOG

Toolbox - Fix Your FILEGROWTH

2017/10/09

One of the items we usually flag in reports is FILEGROWTH by percentage. It doesn't help that for m...

FORUM

Changing MDF and LDF FILEGROWTH

2014/10/21

Hi all! I am interested in changing the filegrowth property of both my mdf and ldf on several hundr...

FORUM

Determining Filegrowth for a completely new database

2007/12/08

How can i determine (by calculations or other means ) the percentage of filegrowth for a newly creat...

FORUM

Working with multiple Database change Release.

2008/07/16

Latest database change script release should check for previous database change too