SQL Server Maintenance Plans: What to Include, What to Avoid and How to Schedule Them

Every production SQL Server needs routine maintenance: backups, integrity checks, statistics, index care and history cleanup. SQL Server’s built-in maintenance plans are the quickest way to schedule it. But a plan built with the wizard’s defaults can do as much harm as good: shrinking databases every night, rebuilding every index whether it needs it or not, or deleting the full backup that your differentials depend on.

This guide explains what maintenance plans are, which tasks a healthy plan should include, which ones to avoid, how to order and schedule them, and when a script-based solution is the better choice.

What Is a SQL Server Maintenance Plan?

A maintenance plan is a workflow of maintenance tasks that you design in SQL Server Management Studio, either with the Maintenance Plan Wizard or in the designer. Behind the scenes, SQL Server stores each plan as an Integration Services (SSIS) package, and a SQL Server Agent job runs it on a schedule. Tasks can be grouped into subplans with different schedules, for example one subplan for log backups every 15 minutes and another for weekly integrity checks.

The Tasks: What to Use and What to Avoid

Backups

Backups are the core of any plan. Use the full, differential and log backup tasks to match the schedule your RPO and RTO require. Turn on compression and “Verify backup integrity” / checksum, write each backup to its own dated file on a separate backup location, and make sure the plan covers new databases automatically by selecting “All user databases” rather than a fixed list. How to choose the schedule is explained in How to Build a SQL Server Backup Plan.

Integrity checks

The Check Database Integrity task runs DBCC CHECKDB. Schedule it so that the gap between two successful checks is shorter than your backup retention; otherwise corruption can reach every backup you have. For large databases, the “Physical only” option shortens the run; combine it with a periodic full check. See DBCC CHECKDB in SQL Server.

Statistics and indexes

This is where wizard defaults cause the most waste. Microsoft’s current guidance on index maintenance is clear:

• Index maintenance is resource-intensive, and its benefit ranges from vital to minuscule depending on the workload. It shouldn’t be done indiscriminately, or based on fixed fragmentation thresholds alone.
• Much of the improvement people see after rebuilding indexes actually comes from the statistics update that a rebuild performs. Updating statistics often achieves the same benefit at a fraction of the cost.
• Reorganize is less resource-intensive than rebuild, is always online, and should be the preferred method unless there’s a specific reason to rebuild.

In practice: schedule Update Statistics regularly, and limit the index tasks with the fragmentation and page-count filters available in the task options (since SQL Server 2016), so only large, genuinely fragmented indexes are touched. Don’t run Rebuild Index and Reorganize Index on the same indexes, and don’t follow a rebuild with a statistics update on the same indexes: a rebuild already updates their statistics with a full scan. For a low-overhead alternative on newer platforms, see SQL Server Automatic Index Compaction.

Never shrink as routine maintenance

Cleanup: history and old backup files

• Clean Up History removes old backup/restore history, Agent job history and maintenance plan history from msdb. Without it, msdb keeps growing and history queries slow down. Keeping a few weeks of history is usually enough; see Best Practices for SQL Server Agent Job History Retention.
• Maintenance Cleanup deletes backup files older than a given age, by file extension. It doesn’t know about backup chains. If it deletes last week’s full backup while this week’s differentials still depend on it, those differentials become useless. Set the age so that a complete set (full + differentials + logs) is always kept, and use separate cleanup tasks with different ages for .bak and .trn files if needed.

Order and Schedule

A typical layout with separate subplans:

Running the integrity check before the weekly full backup means you know the database was clean shortly before the backup that the following week’s restores will depend on. Keep heavy tasks out of each other’s way: the full backup, integrity check and index maintenance all read the whole database and compete for the same I/O.

Monitor the Plans, Not Just Their Existence

A maintenance plan that fails silently is worse than none, because everyone assumes it works. Make sure that:

• Every plan’s Agent job notifies an operator on failure. See Keep Your Business Secure Using SQL Agent Notifications.
• Plans write a report or log so you can see what each task actually did.
• You check regularly that every database has a recent backup and a recent successful CHECKDB, with the queries in our backup plan and CHECKDB guides.
• The job owner is a stable account, not a person’s login that will be disabled when they leave. See Why Owners of SQL Agent Jobs Should Matter.

Maintenance Plans or Scripts?

Maintenance plans are a good fit for small and medium environments where simplicity matters most. As environments grow, script-based solutions become more practical: they’re easier to version, deploy to many servers and fine-tune per database. A widely used free option in the SQL Server community is Ola Hallengren’s SQL Server Maintenance Solution, which covers backups, integrity checks, and index and statistics maintenance with fragmentation-based logic and detailed logging. Microsoft also publishes the Adaptive Index Defrag procedure for index and statistics maintenance. Whichever you choose, the principles above stay the same.

Frequently Asked Questions