| Lesson 11 | Database Maintenance Plan Wizard |
| Objective | Use the Maintenance Plan Wizard in SQL Server 2025 to build a graphical maintenance routine, understand what it actually generates behind the scenes, and know the direct T-SQL equivalent of each step it walks through. |
The Maintenance Plan Wizard remains the supported, graphical way to build a routine maintenance job in SQL Server 2025 (17.x). It still covers the same broad territory it always has: database integrity checks, index and statistics maintenance, scheduled backups, reports, and logging. Two things about it have changed since an older description of this lesson was written, and both are worth correcting directly before anything else, since getting them wrong would undo the accurate picture the previous lesson in this module just finished building around sqlmaint.exe.
An older version of this lesson described launching the wizard from SQL Server Enterprise Manager, by selecting a server group, selecting the SQL Server, and choosing Tools, Database Maintenance from the menu. It also stated that the wizard creates a SQL Server Agent job that runs sqlmaint.exe. Both of those are specifically SQL Server 2000 era facts, and neither one describes current SQL Server.
Launch path, corrected. Enterprise Manager was retired after SQL Server 2000, replaced by SQL Server Management Studio starting with SQL Server 2005. In current SSMS, SSMS 22 being Microsoft's current recommendation for working against SQL Server 2025, the path is Object Explorer, Management, Maintenance Plans, then right-click and choose Maintenance Plan Wizard.
Execution mechanism, corrected. Microsoft's current documentation is explicit on this point: in the Database Engine, a maintenance plan creates an Integration Services package, which a SQL Server Agent job then runs. The wizard does not generate a call to sqlmaint.exe. The previous lesson in this module covered sqlmaint.exe directly and why its stored procedure wrapper, xp_sqlmaint, has been on Microsoft's deprecated features list since SQL Server 2008. The Maintenance Plan Wizard was already its own separate, SSIS-based mechanism well before that deprecation took effect, not a graphical front end built on top of sqlmaint.exe, which is a meaningfully different thing to understand for anyone troubleshooting why a plan behaves the way it does.
sysadmin fixed server role to create a maintenance plan.Four stages cover the ground an older description of this lesson summarized as reorganizing, testing integrity with DBCC commands, scheduling backups, and generating reports and logs. Each stage below corresponds to a step in the wizard itself and to a specific, current T-SQL equivalent worth knowing, whether a plan ends up built graphically or by hand as individual job steps, the approach the previous lesson demonstrated directly.
sysadmin.
Name the plan, choose which tasks it will run, and decide whether each task gets its own schedule or shares one with the others. This is also the point at which it is worth confirming, rather than assuming, the actual state of each database the plan will touch.
SELECT name, state_desc, recovery_model_desc
FROM sys.databases
WHERE name = N'SalesDB';
Checking a database's current state and recovery model before including it in a plan matters because the recovery model in particular determines whether a log backup task is even meaningful for that database. A database left in the SIMPLE recovery model has no log chain to back up, whatever the plan's schedule says.
DBCC CHECKDB on a scheduled window, and escalate any error rather than letting a failed check pass silently. Target index maintenance per index rather than blanket-applying one action to every index in a database, choosing REORGANIZE or REBUILD based on assessed fragmentation. Update statistics where the workload actually needs it, since reorganizing an index does not refresh its statistics as a side effect. The modernized defaults worth carrying forward: measure page density and query performance rather than rebuilding every index on a fixed schedule, avoid routine shrink-and-regrow cycles, never automate a DBCC repair option as part of ordinary maintenance, and prefer restoring from a known-good backup over repair when corruption is actually found.
DBCC CHECKDB (N'SalesDB') WITH NO_INFOMSGS;
ALL_ERRORMSGS sometimes appears alongside NO_INFOMSGS in older examples of this command. Current SQL Server documentation notes that every error message is shown by default regardless of whether this option is specified, so including it is harmless but has no actual effect either way; it is not deprecated, simply redundant.
Index maintenance is chosen per index, not applied blanket-wide across a database:
ALTER INDEX IX_Orders_OrderDate ON dbo.Orders REORGANIZE;
Reorganizing and rebuilding are not interchangeable, and the choice between them should rest on measured fragmentation rather than a fixed nightly schedule applied to every index regardless of its actual condition. Reorganizing does not refresh statistics as a side effect; rebuilding does, since a rebuild reads and rewrites the entire index. The previous lesson in this module covered UPDATE STATISTICS directly, for exactly the cases a reorganize, or an index that goes untouched for a stretch, leaves stale:
UPDATE STATISTICS dbo.Orders WITH SAMPLE 20 PERCENT;
FULL or BULK_LOGGED recovery, a transaction log backup, each written with a unique file name per run and a checksum. RESTORE VERIFYONLY confirms a backup file is structurally readable, a different thing from an actual tested restore. The storage and recovery policy decisions behind the schedule: grant the engine's service account access to the backup destination, take a fresh full backup immediately after switching a database out of SIMPLE recovery, choose a log-backup frequency based on an acceptable amount of data loss, keep every log backup needed to restore forward from the last full backup rather than deleting any of them early, and only delete backup files under a reviewed retention rule, never by an arbitrary age cutoff picked without checking the chain.
BACKUP DATABASE SalesDB
TO DISK = N'E:\SQLBackups\SalesDB_full.bak'
WITH CHECKSUM;
BACKUP LOG SalesDB
TO DISK = N'E:\SQLBackups\SalesDB_log.trn'
WITH CHECKSUM;
A log backup is only meaningful for a database in the FULL or BULK_LOGGED recovery model, and switching a database into FULL from SIMPLE requires taking a new full backup before log backups can begin forming a usable, restorable chain. Running RESTORE VERIFYONLY against the resulting file is worth doing as a routine check, but it is not the same thing as a tested restore into a working database, a distinction that matters for anything actually called a disaster recovery plan rather than just a backup schedule nobody has confirmed works end to end.
RESTORE VERIFYONLY
FROM DISK = N'E:\SQLBackups\SalesDB_full.bak'
WITH CHECKSUM;
msdb.dbo.sysjobhistory, while a wizard-generated maintenance plan additionally logs to msdb.dbo.sysmaintplan_log and msdb.dbo.sysmaintplan_logdetail. Database Mail and an Agent operator both need separate configuration before a failure notification actually reaches anyone; history cleanup removes log records, not the backup files themselves, which need their own retention policy entirely.
Building the equivalent job by hand in SSMS, rather than letting the wizard generate it, follows the same sequence shown in the image above: name and own the job, add a T-SQL step with the reviewed command against the correct database, set what happens on success and on failure, attach a schedule, and test the job manually at least once before turning on its recurring schedule unattended.
History for the two execution paths lives in different places, worth knowing before searching the wrong table while troubleshooting a failed overnight run: an Agent job's own run history is in msdb.dbo.sysjobhistory, while a wizard-generated maintenance plan additionally logs to msdb.dbo.sysmaintplan_log and msdb.dbo.sysmaintplan_logdetail. Database Mail and an Agent operator need to be configured separately for failure notifications to actually reach anyone; a job can fail silently into its own history table with no alert sent at all if that configuration step gets skipped.
A maintenance plan that silently stops doing its job is often worse than one that never ran at all, since the gap between "assumed working" and "actually working" can go unnoticed for weeks. A handful of specific failure modes come up often enough to check deliberately rather than assume away:
msdb.dbo.sysjobhistory, and notify absolutely no one, because Database Mail and an Agent operator were never set up. The job history looks complete; nobody is reading it.SIMPLE recovery, during a restore from another environment, say, breaks the log backup chain without necessarily throwing an error on the next full backup. The symptom shows up later, as a missing or impossible point-in-time restore, not at the moment the chain actually broke.Each of these connects back to this module's earlier monitoring lessons: checking sys.dm_db_log_space_usage or DBCC OPENTRAN would surface the recovery-model-drift problem directly, and reviewing Agent job history on a regular cadence, not just when something is visibly broken, is what catches the silent no-operator failure before it has gone unnoticed for a month.
| Approach | Best fit | Underlying mechanism |
|---|---|---|
| Maintenance Plan Wizard | A uniform routine across many similar databases, built and maintained by someone comfortable in a graphical designer | Integration Services package, run by a SQL Server Agent job |
| Hand-written T-SQL job steps | A small number of databases needing different handling from one another, or precise control over exactly what runs and when | Direct T-SQL, run as individual Agent job steps against the Database Engine |
| Ola Hallengren's Maintenance Solution | Many databases on one instance needing fragmentation-aware, change-aware maintenance without a custom job written for each one | T-SQL stored procedures, scheduled through Agent jobs the solution's own install script creates |
None of the three is strictly better than the others in every situation; they trade off ease of initial setup, how much a routine can vary database to database, and how much ongoing hand-maintenance the routine itself needs as the databases it covers change over time.
Combining the four stages above into one coherent routine for this course's running example database: a nightly job checks SalesDB's integrity first, reorganizes or rebuilds indexes based on that check and measured fragmentation, takes a full backup only if the integrity check passed, and separately schedules transaction log backups every 15 minutes during the day for a tighter recovery point objective than a once-nightly full backup alone could provide. Database Mail and an Agent operator are configured once, up front, so a failed step actually generates a notification rather than a silently accumulating history entry nobody checks until something has already gone wrong.
Whether that routine gets built through the Maintenance Plan Wizard's graphical steps or as individual Agent job steps running direct T-SQL, the approach the previous lesson demonstrated by hand, the underlying mechanism for the graphical path is the same either way: an Integration Services package, scheduled and run by SQL Server Agent, never sqlmaint.exe. Choosing between the two approaches mostly comes down to how much the routine needs to vary from database to database on the same instance. The wizard's plan subplans handle a uniform routine applied across many databases cleanly; a hand-written set of Agent job steps, or a community solution like Ola Hallengren's scripts already introduced earlier in this module, tends to scale better once different databases genuinely need different handling, different schedules, or different retention rules from one another.
This module opened with the basic configuration and prerequisites for monitoring a SQL Server instance, moved through importing and exporting data, and spent its later lessons on the DBCC commands and dynamic management views that reveal the actual state of a database's transaction log, consistency, and statistics at any given moment. The previous lesson turned that monitoring work into a standing routine built from direct T-SQL and SQL Server Agent job steps. This lesson covers the same ground through the Maintenance Plan Wizard's graphical interface instead, built on an Integration Services package rather than hand-written T-SQL, but arriving at functionally the same place: a scheduled, logged, alertable routine rather than a set of commands someone has to remember to run by hand. Whichever approach ends up built for a given database or instance, the module's underlying lesson carries through either way: monitoring tells you what is true right now, and automated maintenance, whichever of the three approaches builds it, is what keeps that state from quietly drifting in the stretches between the times anyone thinks to check.
The next lesson concludes this module.