Changing the autogrowth settings for a set of databases

SharePoint simply ignores the autogrowth settings of your model database and as such you need to make changes to the autogrowth settings of a lot of databases after installation or after adding new content databases. The script below can be used to create a batch script to save you from the tedious task of going over each and every database.

Have fun with it!

-- begin settings
DECLARE @data_FILEGROWTH nvarchar(50) = '10GB'
DECLARE @data_MAXSIZE nvarchar(50) = '200GB'

DECLARE @log_FILEGROWTH nvarchar(50) = '128MB'
DECLARE @log_MAXSIZE nvarchar(50) = '1GB'

DECLARE @database_name_inclusion_filter nvarchar(max) = '%'
DECLARE @database_name_exclusion_filter nvarchar(max) = ''
-- end settings

USE master

DECLARE @database_name nvarchar(max)
DECLARE @database_id int
DECLARE @file_name nvarchar(max)
DECLARE @file_type int

DECLARE databases_cursor CURSOR for
	SELECT database_id, name FROM sys.databases
	WHERE owner_sid != 0x01

OPEN databases_cursor

FETCH NEXT FROM databases_cursor 
INTO @database_id, @database_name

WHILE @@FETCH_STATUS = 0
BEGIN
	IF @database_name like @database_name_inclusion_filter AND @database_name not like @database_name_exclusion_filter
	BEGIN
		PRINT '--Database ' +  @database_name

		DECLARE files_cursor CURSOR for
			SELECT name, [type] FROM sys.master_files
			WHERE database_id = @database_id

		OPEN files_cursor

		FETCH NEXT FROM files_cursor 
		INTO @file_name, @file_type
	
		WHILE @@FETCH_STATUS = 0
		BEGIN
			IF @file_type = 0
			BEGIN
				print 'ALTER DATABASE ' + @database_name +
					' MODIFY FILE (NAME=' + @file_name + ',MAXSIZE=' + @data_MAXSIZE + ',FILEGROWTH=' + @data_FILEGROWTH + ');'
			END

			IF @file_type = 1
			BEGIN
				print 'ALTER DATABASE ' + @database_name +
					' MODIFY FILE (NAME=' + @file_name + ',MAXSIZE=' + @log_MAXSIZE + ',FILEGROWTH=' + @log_FILEGROWTH + ');'
			END

			FETCH NEXT FROM files_cursor 
			INTO @file_name, @file_type
		END

		CLOSE files_cursor;
		DEALLOCATE files_cursor;
	END


	FETCH NEXT FROM databases_cursor 
	INTO @database_id, @database_name
END

CLOSE databases_cursor;
DEALLOCATE databases_cursor;