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
No Comments