Alter Growth Rate of All Database Files

To avoid unexpected and frequent growth of database files, it is important for a DBA to properly analyze the production databases file growth and setting them up with the appropriate rate.

Script is to change growth rate of all databases to 500MB.

	/*
	Script By: Amna Asif for http://sqlcache.blogspot.com/
	Purpose: To Convert Growth rate of databases
	*/
	USE MASTER
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	SET ARITHABORT ON
	GO
	DECLARE @DBName NVARCHAR(255)
	DECLARE @FileRrowthRate VARCHAR(50)
	DECLARE @Qry NVARCHAR(500),
	@DBLogicalFileName VARCHAR(200)
	SET @FileRrowthRate = 512000
	DECLARE DatabaseList CURSOR
	FOR SELECT DISTINCT
	db.name
	FROM sys.master_files mfile
	INNER JOIN sys.databases db ON mfile.database_id = db.database_id
	WHERE -- is_percent_growth=1 and /* Growth of the file is a percentage then value= 1 anf iF Absolute growth size in pages then value = 0*/
	db.state_desc = 'ONLINE'
	AND db.is_read_only = 0
	AND ( db.database_id > 4
	OR db.name = 'model'
	)
	ORDER BY name
	OPEN DatabaseList
	FETCH NEXT FROM DatabaseList INTO @DBName
	WHILE @@FETCH_STATUS = 0
	BEGIN
	--------------------------------Data File Growth Rate --------------------------
	--------------------------------------------------------------------------------
	SELECT @Qry = ' SELECT @DBLogicalFileNameOUT = NAME
	FROM ' + @DBName + '.sys.master_files AS mf
	WHERE DB_NAME(database_id)=''' + @DBName + '''
	AND TYPE_DESC=''ROWS'' '
	EXEC sp_executesql @Qry, N'@DBLogicalFileNameOUT nvarchar(200) OUTPUT', @DBLogicalFileNameOUT = @DBLogicalFileName OUTPUT ;
	PRINT @DBName
	EXEC
	( 'ALTER DATABASE [' + @DBName + ']
	MODIFY FILE ( NAME = N''' + @DBLogicalFileName + ''', FILEGROWTH = ' + @FileRrowthRate + 'KB )'
	)
	--------------------------------Log File Growth Rate --------------------------
	-----------------------------------------------------------------------------------
	SELECT @Qry = ' SELECT @DBLogicalFileNameOUT = NAME
	FROM ' + @DBName + '.sys.master_files AS mf
	WHERE DB_NAME(database_id)=''' + @DBName + '''
	AND TYPE_DESC=''Log'' '
	EXEC sp_executesql @Qry, N'@DBLogicalFileNameOUT nvarchar(200) OUTPUT', @DBLogicalFileNameOUT = @DBLogicalFileName OUTPUT ;
	EXEC
	( 'ALTER DATABASE [' + @DBName + ']
	MODIFY FILE ( NAME = N''' + @DBLogicalFileName + ''', FILEGROWTH = ' + @FileRrowthRate + 'KB )'
	)
	FETCH NEXT FROM DatabaseList INTO @DBName
	END
	CLOSE DatabaseList
	DEALLOCATE DatabaseList

view raw ChangeGrowthRate.sql hosted with ❤ by GitHub