Automate Database Maintenance in SQL Server 2025

What sqlmaint Could Do

This is kept here for historical context and because the switches below are exactly what the exercise linked at the end of this lesson still tests.

• Database integrity checks: -CkDB, -CkDBNoIdx, -CkAl, -CkAlNoIdx, -CkCat
• Statistics: -UpdOptiStats <sample_percent>, optionally combined with -SupportComputedColumn
• Index maintenance: -RebldIdx <free_space_percent>
• Backups: -BkUpDB or -BkUpLog, with -BkUpMedia DISK|TAPE, plus -UseDefDir, -CrBkSubDir, -DelBkUps <time>, -VrfyBackup, and -BkUpOnlyIfClean
• Reporting: -Rpt, -HtmlRpt, -DelHtmlRpt <time>, -WriteHistory
• Plan scoping: -PlanName or -PlanID, which restricts the run to that maintenance plan's own list of databases rather than every database on the instance

REM Windows Authentication is used by default if -U is omitted
sqlmaint -S MyServer\SQL2025 -D SalesDB -CkDB -CkAl -CkCat -Rpt "C:\DBMaint\SalesDB_chk.rpt" -WriteHistory

REM Update statistics with a 15 percent sample for every database in a plan
sqlmaint -S MyServer -PlanName "UserDBs" -UpdOptiStats 15 -WriteHistory

REM Back up to the default backup directory, one subdirectory per database, keep the last two weeks
sqlmaint -S MyServer -PlanName "UserDBs" -BkUpDB -BkUpMedia DISK -UseDefDir -CrBkSubDir -DelBkUps 2weeks -VrfyBackup

REM Back up only if the integrity check passes
sqlmaint -S MyServer -D SalesDB -CkDB -BkUpDB "D:\SqlBackups" -BkUpMedia DISK -BkUpOnlyIfClean -VrfyBackup

Tip: avoid embedding a password on the command line. Omitting -U and -P falls back to Windows Authentication, which is the safer default for a job running under a service account rather than an interactive login.

Return Codes and History

• Exit code 0 means success; 1 covers any failure, including a failed DBCC check.
• -WriteHistory records each run to msdb.dbo.sysdbmaintplan_history, the same history table SSMS Maintenance Plans still write to today. A report requested through -Rpt or -HtmlRpt is written separately, to the path given, with a timestamp in the filename.

What SQL Server 2022 and 2025 Actually Changed

It is tempting to tie newer SQL Server features directly to the kind of work sqlmaint used to do, and some writing on this topic does exactly that. Three specific claims are worth checking against Microsoft's own documentation before repeating them, because two do not hold up as stated and one was pinned to the wrong SQL Server version.

Degree of Parallelism Feedback Has Nothing to Do With Maintenance or Statistics

Degree of Parallelism (DOP) Feedback is a real Intelligent Query Processing feature, introduced in SQL Server 2022, requiring database compatibility level 160, and not enabled by default. What it actually does: for a query that runs repeatedly, it watches the query's actual elapsed time and wait statistics, and if the configured degree of parallelism looks inefficient for that query, it lowers the DOP for the next execution and keeps the change only if it measurably helped. A regression reverts to the last known good value. None of that touches DBCC CHECKDB or any other maintenance command, and Microsoft's documentation does not describe DOP Feedback as reducing the need to run UPDATE STATISTICS. Those are two separate ideas that sometimes get combined incorrectly: DOP Feedback adjusts parallelism for ordinary, repeating queries, and accurate statistics remain exactly as necessary as before for the query optimizer's row-count estimates. One has nothing to do with the other.

-- DOP Feedback is off by default; enable it at the database level
ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = ON;

-- Disable it for one query without changing the database setting
SELECT OrderID, OrderDate FROM dbo.Orders
WHERE CustomerID = 4821
OPTION (USE HINT('DISABLE_DOP_FEEDBACK'));

Query Store Hints Are Not a Maintenance-Contention Tool

Query Store itself is enabled by default for new databases starting in SQL Server 2022, and it genuinely does capture maintenance operations such as index rebuilds in its own history alongside every other query that runs. Query Store hints, though, are a separate, narrower capability: an OPTION query hint applied to one already-captured query, identified by its query_id, used to force a join type, cap MAXDOP, limit a memory grant, or force a particular cardinality estimator, all without touching the application's own SQL. Microsoft's own guidance on this feature actually runs the opposite direction from a claim that hints resolve contention during index maintenance: it recommends finishing statistics and index maintenance first, since that maintenance often removes the need for a query hint entirely. There is no documented relationship between Query Store hints and ALTER INDEX ... REBUILD.

-- Find the query_id for a specific captured statement
SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id
WHERE qt.query_sql_text LIKE N'%CustomerID = %';

-- Force the legacy cardinality estimator for that one query
EXEC sys.sp_query_store_set_hints
    @query_id = 39,
    @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

ZSTD Backup Compression Is a SQL Server 2025 Feature, Not a SQL Server 2022 One

This version pin is worth correcting directly, since it is easy to find ZSTD attributed to SQL Server 2022 in early secondary coverage of the feature. ZSTD backup compression was introduced in SQL Server 2025, as a third compression algorithm alongside the long-standing MS_XPRESS and the newer QAT-based options, offering faster compression and smaller backup files than the older default. The syntax is a nested clause, not a flat list of comma-separated options:

BACKUP DATABASE SalesDB
TO DISK = N'D:\Backups\SalesDB.bak'
WITH COMPRESSION (ALGORITHM = ZSTD);

Microsoft's own backup compression documentation does not show a separate compression-level option for this clause; the only documented sub-option is ALGORITHM. If a compression-level setting turns out to matter for a particular algorithm, it is worth confirming directly against the BACKUP statement reference rather than assuming a form that has circulated in secondary write-ups. ZSTD can also be made the server-wide default compression algorithm without touching individual backup scripts:

EXEC sp_configure 'backup compression algorithm', 3;
RECONFIGURE;

Microsoft Entra ID Authentication for On-Premises SQL Server

One claim does hold up, with a caveat worth adding. On-premises SQL Server, whether running on Windows or Linux, has supported Microsoft Entra ID authentication (formerly Azure AD) since SQL Server 2022. It is not automatic, though, and it is not something a maintenance script picks up just because the instance has been upgraded. The standard setup path registers the SQL Server instance and its host with Azure Arc; a manual, Arc-free alternative exists for Windows only, involving certificates, registry settings, and an Entra application registration. Standalone instances and Always On availability groups are supported; failover cluster instances are not. For a maintenance job or connection string being modernized away from a command-line password, Entra ID authentication is a real, current option, but it is a deliberate configuration step with its own prerequisites, not a side effect of running a newer SQL Server version.

Modern Alternatives, Recommended Over sqlmaint

• Maintenance Plans in SSMS, built on SQL Server Agent and SSIS under the hood, with built-in logging, scheduling, and history writing to the same msdb.dbo.sysdbmaintplan_history table sqlmaint used. A Maintenance Plan covers the same integrity-check, statistics, index, and backup ground through a graphical designer instead of command-line switches, and the SSMS Maintenance Plan Wizard is this course's own next lesson.
• dtexec, which runs an SSIS package, including a Maintenance Plan exported as a package, from the command line or a scheduled task. This is the closer like-for-like replacement for anyone whose existing automation specifically needs a command-line entry point rather than a graphical tool.
• Direct T-SQL, scheduled through SQL Server Agent job steps or run interactively via sqlcmd: DBCC CHECKDB, BACKUP DATABASE ... WITH COMPRESSION, ALTER INDEX ... REBUILD, and UPDATE STATISTICS, the same commands this module's earlier lessons already covered individually, one at a time, rather than bundled behind a single utility's switches.
• Ola Hallengren's Maintenance Solution, a free, widely used, community-trusted set of T-SQL scripts already introduced in this module's statistics lesson. Its IndexOptimize script and companion backup procedures schedule maintenance intelligently, based on measured fragmentation and change activity, rather than a blind nightly pass over everything, and it remains the most common packaged version of the "direct T-SQL, scheduled by the Agent" approach.

Scheduling and Alerting With SQL Server Agent

A command run by hand is not automation; it is automation only once something else triggers it on a schedule and tells someone when it fails. SQL Server Agent is what sqlmaint was always meant to run under, and it is still what Maintenance Plans and most direct T-SQL replacements run under today. A job made of several steps, each one gated on the previous step's success, is the Agent-native version of what sqlmaint's single-line, multiple-switch invocation used to do in one process.

-- Create the job
EXEC msdb.dbo.sp_add_job @job_name = N'Nightly SalesDB Maintenance';

-- Step 1: integrity check
EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'Nightly SalesDB Maintenance',
    @step_name = N'Integrity Check',
    @subsystem = N'TSQL',
    @command = N'DBCC CHECKDB (SalesDB) WITH NO_INFOMSGS;',
    @on_success_action = 3; -- go to next step

-- Step 2: statistics refresh
EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'Nightly SalesDB Maintenance',
    @step_name = N'Statistics Refresh',
    @subsystem = N'TSQL',
    @command = N'UPDATE STATISTICS SalesDB.dbo.Orders WITH SAMPLE 20 PERCENT;',
    @on_success_action = 3;

-- Schedule it for 2 AM daily
EXEC msdb.dbo.sp_add_jobschedule
    @job_name = N'Nightly SalesDB Maintenance',
    @name = N'Nightly 2AM',
    @freq_type = 4, -- daily
    @active_start_time = 020000;

Each step's failure behavior is set independently, through @on_fail_action, so a failed integrity check can stop the whole job rather than letting a backup run against a database that just failed DBCC CHECKDB, a safeguard sqlmaint's own -BkUpOnlyIfClean switch was built to provide in a single command. Pairing the job with a SQL Server Agent alert on job failure, or on a specific error severity, is what actually notifies someone when a step fails overnight rather than leaving a silent entry in the job history for someone to discover days later. This is also where the earlier lessons in this module come back in directly: the job history and error log this Agent job writes to are exactly the kind of thing the monitoring commands and DMVs from this module's earlier lessons were built to check.

Putting It Together: Replacing One sqlmaint Job With Modern Equivalents

A short walkthrough ties the pieces of this lesson together, since the useful comparison is not any one switch against any one modern command, but a whole job replaced end to end.

Suppose an existing scheduled task runs this nightly, against the SalesDB database:

sqlmaint -S MyServer -D SalesDB -CkDB -UpdOptiStats 20 -BkUpDB "D:\SqlBackups" -BkUpMedia DISK -BkUpOnlyIfClean -VrfyBackup -WriteHistory

That single line does four things in sequence: an integrity check, a statistics refresh at a fixed 20 percent sample, a backup that only runs if the integrity check passed, and a history record of the whole run. Replacing it with direct T-SQL as SQL Server Agent job steps, each step gated on the success of the one before it, looks like this:

-- Step 1: Integrity check
DBCC CHECKDB (SalesDB) WITH NO_INFOMSGS;

-- Step 2: Statistics refresh (runs only if Step 1 succeeded)
UPDATE STATISTICS SalesDB.dbo.Orders WITH SAMPLE 20 PERCENT;
UPDATE STATISTICS SalesDB.dbo.Customers WITH SAMPLE 20 PERCENT;

-- Step 3: Backup, with the newer ZSTD compression this lesson corrected above (runs only if Step 2 succeeded)
BACKUP DATABASE SalesDB
TO DISK = N'D:\SqlBackups\SalesDB.bak'
WITH COMPRESSION (ALGORITHM = ZSTD), CHECKSUM;

The practical difference is not just syntax. Each Agent job step logs and alerts independently, the backup step can use the newer compression algorithm this lesson corrected above, and the whole sequence stops cleanly at whichever step fails instead of sqlmaint's single pass-or-fail exit code for the entire run. For a larger instance with many databases, Ola Hallengren's scripts handle this same gated sequencing automatically, across every database on the instance, without a custom Agent job written by hand for each one.

A Quick Comparison: sqlmaint Switches and Their Modern Equivalents

This Module, From Monitoring to Automation

Stepping back over this module as a whole: it opened with the basic configuration and prerequisites for monitoring a SQL Server instance, moved through importing and exporting data, then spent its last several lessons on the DBCC commands and dynamic management views that reveal what a database's transaction log, consistency, and statistics actually look like at any given moment. This lesson is where that monitoring work turns into a standing routine rather than a one-off investigation. Reading sys.dm_db_log_space_usage or checking sys.dm_db_stats_properties by hand, as earlier lessons covered, tells you the state of one database at one moment; a scheduled Agent job running DBCC CHECKDB, a statistics refresh, and a compressed backup every night, with an alert on failure, is what keeps that state from quietly drifting between the times anyone thinks to check it manually. The modern tools in this lesson, Agent job steps, Maintenance Plans, and Ola Hallengren's scripts, are the automation layer that this module's monitoring commands exist to feed information into and, when something looks wrong, to help diagnose after the fact.

Exercise

Test yourself on sqlmaint switches and safe usage: Using sqlmaint.exe, Exercise

The next lesson covers the graphical Maintenance Plan Wizard in SSMS, the direct, currently supported successor to everything sqlmaint used to do from the command line.