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) 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 in the machine.config:     So it will look like: Hard refresh the browser to clean the cache Since MXSuite is launched in a browser, the cache may impact the interface. Sometimes, weird text may appear, or icons for menu items may not be visible. Often this happens after an update to the next version of MXSuite. In such cases, the following steps can help to fix this. Option 1: via menu In your browser, go to the three dots -> More tools -> Developer tools In the left-upper corner of the browser, find the refresh button, right-click on it and select Empty Cache and Hard Reload Option 2: via keyboard When you have opened MXSuite, try the following key options (depends per browser): Shift + click the Reload button in the browser Ctrl + F5 Ctrl + Shift + R     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 Modify growth settings In lots of databases, the file growth settings are left as the default value: 1MB or 10%. This is not optimal.  It can cause all kinds of issues, which rear their ugly heads as performance problems. We have created a SQL Server agent job that runs monthly on the first day of the month, setting the growth settings to the proper values according to the database size.  The query to create the job is also attached to this page. Source: SQL RX USE [msdb] GO /****** Object: Job [Mastex - Modify File Growth Settings for All Databases] Script Date: 28-5-2025 13:42:24 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 28-5-2025 13:42:24 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Mastex - Modify File Growth Settings for All Databases', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'Query made by Mastex Software 2025', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [BETWEEN 0 AND 10 (MB)] Script Date: 28-5-2025 13:42:25 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 0 AND 10 (MB)', @step_id=1, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'DECLARE @MinDBSize INT = 0 DECLARE @MaxDBSize INT = 10 DECLARE @FileGrowth_MDF VARCHAR(10) = 10 DECLARE @FileGrowth_LDF VARCHAR(10) = 250 -------------------------------------------------------- DECLARE @dbname VARCHAR(200) DECLARE @filename VARCHAR(200) DECLARE @SqlCmd VARCHAR(2000) DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''ROWS'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles --LOG files DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''LOG'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles', @database_name=N'master', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [BETWEEN 10 AND 50 (MB)] Script Date: 28-5-2025 13:42:25 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 10 AND 50 (MB)', @step_id=2, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'DECLARE @MinDBSize INT = 10 DECLARE @MaxDBSize INT = 50 DECLARE @FileGrowth_MDF VARCHAR(10) = 20 DECLARE @FileGrowth_LDF VARCHAR(10) = 250 -------------------------------------------------------- DECLARE @dbname VARCHAR(200) DECLARE @filename VARCHAR(200) DECLARE @SqlCmd VARCHAR(2000) DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''ROWS'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles --LOG files DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''LOG'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles', @database_name=N'master', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [BETWEEN 50 AND 200 (MB)] Script Date: 28-5-2025 13:42:25 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 50 AND 200 (MB)', @step_id=3, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'DECLARE @MinDBSize INT = 50 DECLARE @MaxDBSize INT = 200 DECLARE @FileGrowth_MDF VARCHAR(10) = 50 DECLARE @FileGrowth_LDF VARCHAR(10) = 250 -------------------------------------------------------- DECLARE @dbname VARCHAR(200) DECLARE @filename VARCHAR(200) DECLARE @SqlCmd VARCHAR(2000) DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''ROWS'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles --LOG files DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''LOG'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles', @database_name=N'master', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [BETWEEN 200 AND 1.000 (MB)] Script Date: 28-5-2025 13:42:25 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 200 AND 1.000 (MB)', @step_id=4, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'DECLARE @MinDBSize INT = 200 DECLARE @MaxDBSize INT = 1000 DECLARE @FileGrowth_MDF VARCHAR(10) = 100 DECLARE @FileGrowth_LDF VARCHAR(10) = 250 -------------------------------------------------------- DECLARE @dbname VARCHAR(200) DECLARE @filename VARCHAR(200) DECLARE @SqlCmd VARCHAR(2000) DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''ROWS'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles --LOG files DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''LOG'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles', @database_name=N'master', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [BETWEEN 1.000 AND 2.000 (MB)] Script Date: 28-5-2025 13:42:25 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 1.000 AND 2.000 (MB)', @step_id=5, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'DECLARE @MinDBSize INT = 1000 DECLARE @MaxDBSize INT = 2000 DECLARE @FileGrowth_MDF VARCHAR(10) = 250 DECLARE @FileGrowth_LDF VARCHAR(10) = 250 -------------------------------------------------------- DECLARE @dbname VARCHAR(200) DECLARE @filename VARCHAR(200) DECLARE @SqlCmd VARCHAR(2000) DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''ROWS'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles --LOG files DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''LOG'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles', @database_name=N'master', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [BETWEEN 2.000 AND 4.500 (MB)] Script Date: 28-5-2025 13:42:25 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 2.000 AND 4.500 (MB)', @step_id=6, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'DECLARE @MinDBSize INT = 2000 DECLARE @MaxDBSize INT = 4500 DECLARE @FileGrowth_MDF VARCHAR(10) = 250 DECLARE @FileGrowth_LDF VARCHAR(10) = 500 -------------------------------------------------------- DECLARE @dbname VARCHAR(200) DECLARE @filename VARCHAR(200) DECLARE @SqlCmd VARCHAR(2000) DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''ROWS'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles --LOG files DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''LOG'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles', @database_name=N'master', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [BETWEEN 4.500 AND 20.000 (MB)] Script Date: 28-5-2025 13:42:25 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 4.500 AND 20.000 (MB)', @step_id=7, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'DECLARE @MinDBSize INT = 4500 DECLARE @MaxDBSize INT = 20000 DECLARE @FileGrowth_MDF VARCHAR(10) = 250 DECLARE @FileGrowth_LDF VARCHAR(10) = 1000 -------------------------------------------------------- DECLARE @dbname VARCHAR(200) DECLARE @filename VARCHAR(200) DECLARE @SqlCmd VARCHAR(2000) DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''ROWS'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles --LOG files DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''LOG'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles', @database_name=N'master', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [BETWEEN 20.000 AND 40.000 (MB)] Script Date: 28-5-2025 13:42:25 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 20.000 AND 40.000 (MB)', @step_id=8, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'DECLARE @MinDBSize INT = 20000 DECLARE @MaxDBSize INT = 40000 DECLARE @FileGrowth_MDF VARCHAR(10) = 500 DECLARE @FileGrowth_LDF VARCHAR(10) = 1000 -------------------------------------------------------- DECLARE @dbname VARCHAR(200) DECLARE @filename VARCHAR(200) DECLARE @SqlCmd VARCHAR(2000) DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''ROWS'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles --LOG files DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''LOG'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles', @database_name=N'master', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [BETWEEN 40.000 AND 100.000 (MB)] Script Date: 28-5-2025 13:42:25 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 40.000 AND 100.000 (MB)', @step_id=9, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'DECLARE @MinDBSize INT = 40000 DECLARE @MaxDBSize INT = 100000 DECLARE @FileGrowth_MDF VARCHAR(10) = 1000 DECLARE @FileGrowth_LDF VARCHAR(10) = 1000 -------------------------------------------------------- DECLARE @dbname VARCHAR(200) DECLARE @filename VARCHAR(200) DECLARE @SqlCmd VARCHAR(2000) DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''ROWS'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles --LOG files DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''LOG'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles', @database_name=N'master', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [BETWEEN 100.000 AND 200.000 (MB)] Script Date: 28-5-2025 13:42:25 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 100.000 AND 200.000 (MB)', @step_id=10, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'DECLARE @MinDBSize INT = 100000 DECLARE @MaxDBSize INT = 200000 DECLARE @FileGrowth_MDF VARCHAR(10) = 2000 DECLARE @FileGrowth_LDF VARCHAR(10) = 1000 -------------------------------------------------------- DECLARE @dbname VARCHAR(200) DECLARE @filename VARCHAR(200) DECLARE @SqlCmd VARCHAR(2000) DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''ROWS'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles --LOG files DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''LOG'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles', @database_name=N'master', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [BETWEEN 200.000 AND 300.000 (MB)] Script Date: 28-5-2025 13:42:25 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 200.000 AND 300.000 (MB)', @step_id=11, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'DECLARE @MinDBSize INT = 200000 DECLARE @MaxDBSize INT = 300000 DECLARE @FileGrowth_MDF VARCHAR(10) = 3000 DECLARE @FileGrowth_LDF VARCHAR(10) = 2000 -------------------------------------------------------- DECLARE @dbname VARCHAR(200) DECLARE @filename VARCHAR(200) DECLARE @SqlCmd VARCHAR(2000) DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''ROWS'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles --LOG files DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''LOG'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles', @database_name=N'master', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [BETWEEN 300.000 AND 900.000 (MB)] Script Date: 28-5-2025 13:42:25 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BETWEEN 300.000 AND 900.000 (MB)', @step_id=12, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'DECLARE @MinDBSize INT = 300000 DECLARE @MaxDBSize INT = 900000 DECLARE @FileGrowth_MDF VARCHAR(10) = 3000 DECLARE @FileGrowth_LDF VARCHAR(10) = 2000 -------------------------------------------------------- DECLARE @dbname VARCHAR(200) DECLARE @filename VARCHAR(200) DECLARE @SqlCmd VARCHAR(2000) DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''ROWS'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_MDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles --LOG files DECLARE dbfiles CURSOR FOR SELECT sd.name AS DBName, mf.name AS LogicalName FROM sys.master_files mf JOIN sys.databases sd ON sd.database_id = mf.database_id WHERE DB_NAME(mf.database_id) NOT IN (''master'',''model'',''msdb'',''tempdb'') AND CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL,size)/128)) BETWEEN @MinDBSize AND @MaxDBSize --(MB) Change to correct size range AND sd.name NOT IN (SELECT d.name FROM sys.databases d INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id WHERE rs.role_desc = ''SECONDARY'') -- exclude dbs in AG secondary AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline AND mf.type_desc = ''LOG'' -- ROWS or LOG OPEN dbfiles FETCH NEXT FROM dbfiles INTO @dbname, @filename WHILE @@FETCH_STATUS = 0 BEGIN SET @SqlCmd = ''ALTER DATABASE ['' + @dbname + ''] MODIFY FILE (NAME = N''''''+@filename+'''''', FILEGROWTH = ''+@FileGrowth_LDF+'')'' --Change to correct MB preference EXEC (@SqlCmd) PRINT @SqlCmd FETCH NEXT FROM dbfiles INTO @dbname, @filename END CLOSE dbfiles DEALLOCATE dbfiles', @database_name=N'master', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Monthly 1st day', @enabled=1, @freq_type=16, @freq_interval=1, @freq_subday_type=1, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=1, @active_start_date=20180801, @active_end_date=99991231, @active_start_time=90000, @active_end_time=235959, @schedule_uid=N'1eeecd0b-8d76-44c9-bea0-740bbe5a1328' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO Change the target recovery time This article describes how to set or change the target recovery time of a database in SQL Server by using SQL Server Management Studio or Transact-SQL. By default, the target recovery time is 0 or 60 seconds, and the database uses  indirect checkpoints . The target recovery time establishes an upper bound on recovery time for this database. This setting takes effect immediately and doesn't require a restart of SQL Server. ALTER DATABASE MXSuite SET TARGET_RECOVERY_TIME = 60 SECONDS; See also Microsoft Learn   Enable query store Since SQL Server 2016, there has been an option to enable a query store. The query store stores details about unique queries: their execution plans, the time they took, the logical reads they required, etc. This would help greatly in identifying performance issues. ALTER DATABASE [MXSuite] SET QUERY_STORE = ON ( OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), -- keeps 30 days of info, up to limit of storage though DATA_FLUSH_INTERVAL_SECONDS = 900, -- how often the data for the query store gets moved from ram to disk, recommended is900 seconds. higher means more memory usage and less disk i/o, lower means the opposite, although it shouldn't have any significant performance impact. MAX_STORAGE_SIZE_MB = 1024, -- occupies max 1gb, i'd suggest 2gb on bigger databases but I don't know how much 1gb can handle INTERVAL_LENGTH_MINUTES = 60, SIZE_BASED_CLEANUP_MODE = AUTO, QUERY_CAPTURE_MODE = AUTO, --filters out insignificant queries, which there are a lot of MAX_PLANS_PER_QUERY = 200 );   Use of Dymo LabelWriter printer in MXSuite In MXSuite you have the option to print labels for a Dymo Labelwriter.  MXSuite V3 : - you can also use the latest Dymo Connect  on the client  (if that LabelWriter type is supported).  - requires the web service which you can enable during installing Dymo Connect 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 Cannot save changes in MXSuite (Error 405 Method Not Allowed) In some cases, the error 405 Method Not Allowed can happen when attempting to save an entry (Crewing working hours, Regions, etc). It can appear as a pop-up message or can be seen when the Save button is opened while the Developer Tools of the browser are opened: A possible reason for this could be the setting in IIS, called WebDAV Publishing. To fix this, please uncheck the option from Windows Features: After the removal, a reboot might be required if the system prompts you for a restart. Otherwise, restart the IIS and the issue is solved. How can I restrict certain file types (like .exe or .cmd) from being uploaded? You can restrict specific file types from being uploaded to MXSuite by configuring Internet Information Services (IIS). This helps improve security by preventing potentially harmful files from being accepted or processed. The most effective way to do this at the server level is by using IIS Request Filtering. This feature allows you to block requests that include specific file extensions before they even reach your application. How? Open IIS Manager Select your MXSuite website Go to the “Request Filtering” feature. Under the “File Name Extensions” tab, you can add extensions such as  .exe and .cmd to the deny list. Once added, IIS will block any requests containing these file types and the file will not be uploaded to MXSuite. Disable old (unsecure) IIS protocols It is highly recommended to disable old protocols like TLS 1.0 TLS 1.1 3DES How to do this Download IIS Crypto developed by Nartac Software :  https://www.nartac.com/Products/IISCrypto Start IIS Crypto as Administrator Disable everything, and then enable the following items: Press Apply Restart the PC (best practice) or restart IIS. TIP: check your MXSuite website via the Site Scanner option