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