Related to third party software
FAQ related to the Windows operating system (OS), Internet Information Services (IIS), SQL Server, etc.
- Installation of Internet Information Services (IIS)
- IIS MXSuite web redirect HTTP to HTTPS
- Sign up for the e-learning platform
- SPF record setting in DNS for SaaS customers
- End of life for Microsoft SQL server
- Set the regional settings of MXSuite
- How to resolve a "Connection timeout" error?
- Use of Dymo LabelWriter printer in MXSuite
- SQL Server optimization
Installation of Internet Information Services (IIS)
To install IIS, at least the following components need to be selected:
Extra add-ons
Before MXSuite can be opened via your browser, the IIS add-on URL Rewrite must be installed at the IIS server.
To check if URL Rewrite is installed, follow the below steps:
- Open IIS manager.
- Check if URL Rewrite is installed
If the icon is not visible, please download and install URL Rewrite here.
IIS MXSuite web redirect HTTP to HTTPS
Once the SSL certificate is installed, MXSuite remains accessible via a regular insecure HTTP connection. To connect securely, visitors must specify the https:// prefix manually when entering MXSuite’s address in their browsers.
To force a secure connection, it is necessary to set up a certain HTTP/HTTPS redirection rule. This way, anyone who enters MXSuite using a link like “mxsuite.company.com” will be redirected to “https://mxsuite.company.com” making the traffic encrypted between the server and the client side.
Configure the redirect
Below are steps to setup a IIS HTTPS redirect:
- Download and install the URL Rewrite module.
- Open the IIS Manager console and select the website you would like to apply the redirection to in the left-side menu
-
Double-click on the URL Rewrite icon.
- Click Add Rule(s) in the right-side menu.
- Select Blank Rule in the Inbound section, then press OK.
- Enter any rule name you wish.
- In the Match URL section:
- Select Matches the Pattern in the Requested URL drop-down menu
- Select Regular Expressions in the Using drop-down menu
- Enter the following pattern in the Match URL section: (.*)
- Check the Ignore case box
- In the Conditions section, select Match all under the Logical Grouping drop-down menu and press Add.
- In the prompted window:
- Enter {HTTPS} as a condition input
- Select Matches the Pattern from the drop-down menu
- Enter ^OFF$ as a pattern
- Press OK
- In the Action section, select Redirect as the action type and specify the following for Redirect URL:
https://{HTTP_HOST}{REQUEST_URI} - Un-check the Append query string box.
- Select the Redirection Type of your choice. The whole Action section should look like this:
4 redirect types of the redirect rule can be selected in that menu:
- Permanent (301) – preferable type in this case, which tells clients that the content of the site is permanently moved to the HTTPS version. Good for SEO, as it brings all the traffic to your HTTPS website making a positive effect on its ranking in search engines.
- Found (302) – should be used only if you moved the content of certain pages to a new place *temporarily*. This way the SEO traffic goes in favour of the previous content’s location. This option is generally not recommended for a HTTP/HTTPS redirect.
- See Other (303) – specific redirect type for GET requests. Not recommended for HTTP/HTTPS.
- Temporary (307) – HTTP/1.1 successor of 302 redirect type. Not recommended for HTTP/HTTPS.OPTION 2: Specify the Redirect Rule as https://{HTTP_HOST}/{R:1} and check the Append query string box. The Action type is also to be set as Redirect.
- Click on Apply on the right side of the Actions menu.
Sign up for the e-learning platform
Follow the steps below to sign up for the e-learning platform of MXSuite.
- Open the URL that you received from us to sign up for the e-learning platform.
- Check if the discounted course is selected and click Proceed.
- If you ever made an account with Teachable, you can now login. If not, click Sign up with email.
- Enter your name, Email and create a password.
- click Sign up.
- You are ready to start! Click Start your course to learn everything about MXSuite. Enjoy!
SPF record setting in DNS for SaaS customers
If your MXSuite is hosted by Mastex Software, it could happen that emails sent from MXSuite are not received or marked as spam. Usually these emails are sent via the email server of Mastex Software. As this email server is not recognized as a valid email server for your domain, you should add the email server to your domain settings.
Solution:
Add to your domain external DNS settings the following SPF records:
IP addresses from where usually SaaS emails are sent:
- 45.128.67.210
- 5.200.20.33/28
To be safe for the future, you could include mxsuite.nl where all IP addresses are included, even when we change them.
This is something that should be done by the domain administrator who can change the domain DNS settings.
SPF records can be tested here: https://mxtoolbox.com/spf.aspx
End of life for Microsoft SQL server
Several versions of SQL Server have reached the end of their life. When MXSuite was first released in 2009, SQL Server 2008 was the standard, followed by some other versions.
The table below shows which SQL Server versions are no longer supported. Mastex Software usually follows the date Microsoft uses as the end date for mainstream support. This means that future versions of MXSuite will not work with old SQL Server versions.
| SQL version |
End-of-life Mainstream support |
| Microsoft SQL Server 2008 | 8 July 20214 |
| Microsoft SQL Server 2008 R2 | 8 July 2014 |
| Microsoft SQL Server 2012 | 11 July 2017 |
| Microsoft SQL Server 2014 | 9 July 2019 |
| Microsoft SQL Server 2016 | 13 July 2021 |
| Microsoft SQL Server 2017 | 11 October 2022 |
| Microsoft SQL Server 2019 | 7 January 2025 |
| Microsoft SQL Server 2022 | 11 January 2028 |
Do you use an SQL Server version on board or in the office that is red in the table? Change it to the newest SQL version as fast as you can.
Do you have a version that is orange in the list? Then it is okay for now, but not for much longer and we strongly advice to upgrade.
Set the regional settings of MXSuite
If you want to use local formatting for date and time, but also for the formatting of the numbers, you can apply the region or cultural setting to the IIS site.
- Open IIS
- Select the site that should be changed
- Open .NET Globalization
- Select the Culture and UI Culture and set Enable Client Based Culture to True
- Apply the settings (right column)
- Restart the website
How to resolve a "Connection timeout" error?
"Connection timeout" is an error that occurs as a result of a script exceeding the maximum timeout value.
The timeout should be added to the following files:
- C:\Windows\Microsoft.NET\Framework\v4.0.XXXX\Config\machine.config
- C:\Windows\Microsoft.NET\Framework64\v4.0.XXXX\Config\machine.config
Note: The framework version could change.
Check in machine.config if the connection timeout is added at the end, just before the last line.
If not, add the following lines at the end (before </configuration> in the machine.config:
<system.transactions>
<defaultSettings distributedTransactionManagerName="" timeout="23:59:00" /> <machineSettings maxTimeout="23:59:00" />
</system.transactions>
So it will look like:
Use of Dymo LabelWriter printer in MXSuite
In MXSuite you have the option to print labels for a Dymo Labelwriter.
MXSuite V2:
- the max allowed version of Dymo software is: Dymo Labelwriter 8.7.5
- download that version here (right mouse click at the version > save as..)
MXSuite V3:
- you can also use the latest Dymo Connect (if that LabelWriter type is supported).
- requires the web service which you can enable during installing Dymo Connect
- the DYMO software should be installed on the IIS server!
The following reference names are used to create the label template:
| Label | Type | Reference name | Additional info |
| Part number | Barcode | PartNumber | Code 39 recommended |
| Company | text | Company | Presented as MXSuite - [Companyname] |
| Location | text | Location | Name of License |
| Name | text | Name | Description of part |
| Part number | text | PartNumberText | |
| Own code | text | OwnCodeText | |
| Storage/Box | text | Storage/Box | both fields storage and box are shown |
| QR code | image | OwnCode |
The following icon is used to generate QR
|
The Reference name of the objects can be set up as following:
- Right mouse click on the text / icon
- Choose Properties
- Go to tab Advanced
- Fill in the text box of Reference name
The label template is built using the label template: Shipping which has the label size of 101mm x 54mm
SQL Server optimization
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
SQL Server optimization
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
SQL Server optimization
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
);