Maintenance plans in SQL Server give us an easy way to organize, configure, and schedule tasks that ensure that the database engine and the databases that are hosted therein are kept in shape.

Maintenance Plans offer a database administrator an opportunity to configure key tasks like indexing, statistics updates, backups, log cleanups, and others. In previous article we’ve already discussed how to create a basic maintenance plan to perform database consistency check. In this article, we shall do a walkthrough of creating a maintenance plan for a database instance that is hosting small databases. In the course of the walkthrough, I will explain the key choices made in each step in the context of an instance with a moderately large number of small databases. The idea is to configure maintenance for these databases without having to do it one by one. The focus on small databases is intended to avoid the performance overhead associated with maintenance operations.

Maintenance Plan for Weekly Tasks

Figure 1: Launch Maintenance Plan Wizard

We launch the Maintenance Plan Wizard from Object Explorer>[Instance Name]>Management>Maintenance Plans (See Figure 1). The first page of the wizard gives us an overview of those tasks that can be configured. While there are other ways to accomplish these tasks using code and job scheduling, the Maintenance Plan Wizard makes it quite easy to accomplish when dealing with a large number of databases hosted on one instance.

#sql server #database administration #database backup #indexes #ssis #sql