Change Database Growth Rate

Inconsistent file growth settings across a server's databases cause unpredictable out-of-space events. This script sets the FILEGROWTH on every data and log file of every online, writable database to the values you choose at the top - either a percentage (include the % sign) or a fixed number of megabytes.

SET NOCOUNT ON;

--File growth can be set to a percentage or a per-megabyte by either
--	including or omitting the percent sign: '10%' vs '10'
DECLARE @DataFileGrowth varchar(10) SET @DataFileGrowth = '10%'
DECLARE @LogFileGrowth  varchar(10) SET @LogFileGrowth  = '10%'

DECLARE @tSQL VarChar(MAX)
DECLARE @DatabaseName sysname
DECLARE @FileName sysname
DECLARE @FileType sysname

CREATE TABLE #DatabaseFiles
(
	[FileName] sysname,
	[FileType] sysname
)

DECLARE DatabaseList CURSOR FAST_FORWARD FOR
	SELECT
		name
	FROM
		sys.databases
	WHERE
		is_read_only = 0
		AND state_desc = 'ONLINE'
OPEN DatabaseList
FETCH FROM DatabaseList INTO @DatabaseName

WHILE(@@FETCH_STATUS = 0)
BEGIN--WHILE
	----------------------------------------------------------------------------------------------------------------------------------
	TRUNCATE TABLE #DatabaseFiles

	SET @tSQL =
		'INSERT INTO #DatabaseFiles' + CHAR(13) + CHAR(10) +
		'SELECT' + CHAR(13) + CHAR(10) +
			'name as [FileName],' + CHAR(13) + CHAR(10) +
			'type_desc AS [FileType]' + CHAR(13) + CHAR(10) +
		'FROM' + CHAR(13) + CHAR(10) +
			'[' + @DatabaseName + '].sys.database_files' + CHAR(13) + CHAR(10)
	EXEC(@tSQL)
	
	DECLARE DatabaseFiles CURSOR FAST_FORWARD FOR
		SELECT
			[FileName],
			[FileType]
		FROM
			#DatabaseFiles
	OPEN DatabaseFiles
	FETCH FROM DatabaseFiles INTO @FileName, @FileType
	
	WHILE(@@FETCH_STATUS = 0)
	BEGIN--WHILE
		----------------------------------------------------------------------------------------------------------------------------------
	
		SET @tSQL = ''
	
		PRINT 'Setting database [' + @DatabaseName + '] file [' + @FileName + '] to ' + @DataFileGrowth + ' growth.'
	
		IF @FileType = 'ROWS'
		BEGIN--IF
			SET @tSQL = 'ALTER DATABASE [' + @DatabaseName + '] MODIFY FILE ( NAME = N''' + @FileName + ''', FILEGROWTH = ' + @DataFileGrowth + ')'
		END ELSE IF @FileType = 'LOG' BEGIN
			SET @tSQL = 'ALTER DATABASE [' + @DatabaseName + '] MODIFY FILE ( NAME = N''' + @FileName + ''', FILEGROWTH = ' + @LogFileGrowth + ')'
		END--IF
		
		EXEC(@tSQL)
	
		----------------------------------------------------------------------------------------------------------------------------------
		FETCH FROM DatabaseFiles INTO @FileName, @FileType
	END--WHILE

	CLOSE DatabaseFiles DEALLOCATE DatabaseFiles
	
	----------------------------------------------------------------------------------------------------------------------------------
	FETCH FROM DatabaseList INTO @DatabaseName
END--WHILE

CLOSE DatabaseList DEALLOCATE DatabaseList

DROP TABLE #DatabaseFiles