SQL Server optimization

Modify growth settings
In lots of databases, the file growth settings are left as the default value: 1MB or 10%. This is not optimal.  It can cause all kinds of issues, which rear their ugly heads as performance problems.
We have created a SQL Server agent job that runs monthly on the first day of the month, setting the growth settings to the proper values according to the database size. 
The query to create the job is also attached to this page.
Source: SQL RX
USE [msdb]
GO
/****** Object:  Job [Mastex - Modify File Growth Settings for All Databases]    Script Date: 28-5-2025 13:42:24 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 28-5-2025 13:42:24 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Mastex - Modify File Growth Settings for All Databases', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Query made by Mastex Software 2025', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [BETWEEN 0 AND 10 (MB)]    Script Date: 28-5-2025 13:42:25 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 0 AND 10 (MB)', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE @MinDBSize INT = 0
DECLARE @MaxDBSize INT = 10
DECLARE @FileGrowth_MDF VARCHAR(10) = 10
DECLARE @FileGrowth_LDF VARCHAR(10) = 250
--------------------------------------------------------
DECLARE @dbname VARCHAR(200) 
DECLARE @filename VARCHAR(200) 
DECLARE @SqlCmd VARCHAR(2000) 
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''ROWS'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles
--LOG files
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''LOG'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [BETWEEN 10 AND 50 (MB)]    Script Date: 28-5-2025 13:42:25 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 10 AND 50 (MB)', 
		@step_id=2, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE @MinDBSize INT = 10
DECLARE @MaxDBSize INT = 50
DECLARE @FileGrowth_MDF VARCHAR(10) = 20
DECLARE @FileGrowth_LDF VARCHAR(10) = 250
--------------------------------------------------------
DECLARE @dbname VARCHAR(200) 
DECLARE @filename VARCHAR(200) 
DECLARE @SqlCmd VARCHAR(2000) 
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''ROWS'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles
--LOG files
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''LOG'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [BETWEEN 50 AND 200 (MB)]    Script Date: 28-5-2025 13:42:25 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 50 AND 200 (MB)', 
		@step_id=3, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE @MinDBSize INT = 50
DECLARE @MaxDBSize INT = 200
DECLARE @FileGrowth_MDF VARCHAR(10) = 50
DECLARE @FileGrowth_LDF VARCHAR(10) = 250
--------------------------------------------------------
DECLARE @dbname VARCHAR(200) 
DECLARE @filename VARCHAR(200) 
DECLARE @SqlCmd VARCHAR(2000) 
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''ROWS'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles
--LOG files
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''LOG'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [BETWEEN 200 AND 1.000 (MB)]    Script Date: 28-5-2025 13:42:25 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 200 AND 1.000 (MB)', 
		@step_id=4, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE @MinDBSize INT = 200
DECLARE @MaxDBSize INT = 1000
DECLARE @FileGrowth_MDF VARCHAR(10) = 100
DECLARE @FileGrowth_LDF VARCHAR(10) = 250
--------------------------------------------------------
DECLARE @dbname VARCHAR(200) 
DECLARE @filename VARCHAR(200) 
DECLARE @SqlCmd VARCHAR(2000) 
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''ROWS'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles
--LOG files
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''LOG'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [BETWEEN 1.000 AND 2.000 (MB)]    Script Date: 28-5-2025 13:42:25 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 1.000 AND 2.000 (MB)', 
		@step_id=5, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE @MinDBSize INT = 1000
DECLARE @MaxDBSize INT = 2000
DECLARE @FileGrowth_MDF VARCHAR(10) = 250
DECLARE @FileGrowth_LDF VARCHAR(10) = 250
--------------------------------------------------------
DECLARE @dbname VARCHAR(200) 
DECLARE @filename VARCHAR(200) 
DECLARE @SqlCmd VARCHAR(2000) 
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''ROWS'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles
--LOG files
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''LOG'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [BETWEEN 2.000 AND 4.500 (MB)]    Script Date: 28-5-2025 13:42:25 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 2.000 AND 4.500 (MB)', 
		@step_id=6, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE @MinDBSize INT = 2000
DECLARE @MaxDBSize INT = 4500
DECLARE @FileGrowth_MDF VARCHAR(10) = 250
DECLARE @FileGrowth_LDF VARCHAR(10) = 500
--------------------------------------------------------
DECLARE @dbname VARCHAR(200) 
DECLARE @filename VARCHAR(200) 
DECLARE @SqlCmd VARCHAR(2000) 
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''ROWS'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles
--LOG files
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''LOG'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [BETWEEN 4.500 AND 20.000 (MB)]    Script Date: 28-5-2025 13:42:25 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 4.500 AND 20.000 (MB)', 
		@step_id=7, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE @MinDBSize INT = 4500
DECLARE @MaxDBSize INT = 20000
DECLARE @FileGrowth_MDF VARCHAR(10) = 250
DECLARE @FileGrowth_LDF VARCHAR(10) = 1000
--------------------------------------------------------
DECLARE @dbname VARCHAR(200) 
DECLARE @filename VARCHAR(200) 
DECLARE @SqlCmd VARCHAR(2000) 
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''ROWS'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles
--LOG files
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''LOG'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [BETWEEN 20.000 AND 40.000 (MB)]    Script Date: 28-5-2025 13:42:25 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 20.000 AND 40.000 (MB)', 
		@step_id=8, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE @MinDBSize INT = 20000
DECLARE @MaxDBSize INT = 40000
DECLARE @FileGrowth_MDF VARCHAR(10) = 500
DECLARE @FileGrowth_LDF VARCHAR(10) = 1000
--------------------------------------------------------
DECLARE @dbname VARCHAR(200) 
DECLARE @filename VARCHAR(200) 
DECLARE @SqlCmd VARCHAR(2000) 
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''ROWS'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles
--LOG files
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''LOG'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [BETWEEN 40.000 AND 100.000 (MB)]    Script Date: 28-5-2025 13:42:25 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 40.000 AND 100.000 (MB)', 
		@step_id=9, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE @MinDBSize INT = 40000
DECLARE @MaxDBSize INT = 100000
DECLARE @FileGrowth_MDF VARCHAR(10) = 1000
DECLARE @FileGrowth_LDF VARCHAR(10) = 1000
--------------------------------------------------------
DECLARE @dbname VARCHAR(200) 
DECLARE @filename VARCHAR(200) 
DECLARE @SqlCmd VARCHAR(2000) 
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''ROWS'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles
--LOG files
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''LOG'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [BETWEEN 100.000 AND 200.000 (MB)]    Script Date: 28-5-2025 13:42:25 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 100.000 AND 200.000 (MB)', 
		@step_id=10, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE @MinDBSize INT = 100000
DECLARE @MaxDBSize INT = 200000
DECLARE @FileGrowth_MDF VARCHAR(10) = 2000
DECLARE @FileGrowth_LDF VARCHAR(10) = 1000
--------------------------------------------------------
DECLARE @dbname VARCHAR(200) 
DECLARE @filename VARCHAR(200) 
DECLARE @SqlCmd VARCHAR(2000) 
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''ROWS'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles
--LOG files
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''LOG'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [BETWEEN 200.000 AND 300.000 (MB)]    Script Date: 28-5-2025 13:42:25 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 200.000 AND 300.000 (MB)', 
		@step_id=11, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE @MinDBSize INT = 200000
DECLARE @MaxDBSize INT = 300000
DECLARE @FileGrowth_MDF VARCHAR(10) = 3000
DECLARE @FileGrowth_LDF VARCHAR(10) = 2000
--------------------------------------------------------
DECLARE @dbname VARCHAR(200) 
DECLARE @filename VARCHAR(200) 
DECLARE @SqlCmd VARCHAR(2000) 
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''ROWS'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles
--LOG files
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''LOG'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [BETWEEN 300.000 AND 900.000 (MB)]    Script Date: 28-5-2025 13:42:25 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 300.000 AND 900.000 (MB)', 
		@step_id=12, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE @MinDBSize INT = 300000
DECLARE @MaxDBSize INT = 900000
DECLARE @FileGrowth_MDF VARCHAR(10) = 3000
DECLARE @FileGrowth_LDF VARCHAR(10) = 2000
--------------------------------------------------------
DECLARE @dbname VARCHAR(200) 
DECLARE @filename VARCHAR(200) 
DECLARE @SqlCmd VARCHAR(2000) 
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''ROWS'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles
--LOG files
DECLARE dbfiles CURSOR FOR
SELECT sd.name AS DBName, mf.name AS LogicalName
FROM sys.master_files mf
JOIN sys.databases sd ON sd.database_id = mf.database_id
WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'')
AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize  --(MB) Change to correct size range
AND sd.name NOT IN (SELECT d.name
			FROM sys.databases d
			INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
			INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
			INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
			WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline 
AND mf.type_desc = ''LOG'' -- ROWS or LOG
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @dbname, @filename
WHILE @@FETCH_STATUS = 0
BEGIN
	 SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference
	 EXEC (@SqlCmd)
	 PRINT @SqlCmd
	 FETCH NEXT FROM dbfiles INTO @dbname, @filename
END
CLOSE dbfiles
DEALLOCATE dbfiles', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Monthly 1st day', 
		@enabled=1, 
		@freq_type=16, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=1, 
		@active_start_date=20180801, 
		@active_end_date=99991231, 
		@active_start_time=90000, 
		@active_end_time=235959, 
		@schedule_uid=N'1eeecd0b-8d76-44c9-bea0-740bbe5a1328'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Change the target recovery time
This article describes how to set or change the target recovery time of a database in SQL Server by using SQL Server Management Studio or Transact-SQL. By default, the target recovery time is 0 or 60 seconds, and the database uses indirect checkpoints. The target recovery time establishes an upper bound on recovery time for this database.
This setting takes effect immediately and doesn't require a restart of SQL Server.
ALTER DATABASE MXSuite
SET TARGET_RECOVERY_TIME = 60 SECONDS;
See also Microsoft Learn
 

Enable query store
Since SQL Server 2016, there has been an option to enable a query store. The query store stores details about unique queries: their execution plans, the time they took, the logical reads they required, etc. This would help greatly in identifying performance issues.
ALTER DATABASE [MXSuite] SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), -- keeps 30 days of info, up to limit of storage though
    DATA_FLUSH_INTERVAL_SECONDS = 900, -- how often the data for the query store gets moved from ram to disk, recommended is900 seconds. higher means more memory usage and less disk i/o, lower means the opposite, although it shouldn't have any significant performance impact. 
    MAX_STORAGE_SIZE_MB = 1024, -- occupies max 1gb, i'd suggest 2gb on bigger databases but I don't know how much 1gb can handle
    INTERVAL_LENGTH_MINUTES = 60,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO, --filters out insignificant queries, which there are a lot of
    MAX_PLANS_PER_QUERY = 200
);