Managing a database server is like owning a piece of
machinery. You need a maintenance plan to keep the machinery running smooth.
Just as your machinery required maintenance, your databases need attention to
ensure great performance. To help keep your databases in tiptop shape, I am
going to walk you through creating a maintenance plan for SQL Server 2000.
Using the Maintenance Plan Wizard
A database maintenance plan is simply a set of tasks that will be performed on
your SQL Server databases automatically. It lessens the burden on an
administrator by automating essential maintenance tasks. Performing these
tasks, which include integrity checks, backups, and database optimization, will
keep your database running at top efficiency.
The SQL Server Maintenance Plan Wizard offers the easiest way to create a
simple plan that protects your data. If you have never performed a maintenance
plan, this is the easiest place to start. As your skills grow, you can delve
into the advanced SQLMAINT tool. The Maintenance Plan Wizard performs the
- Runs database integrity checks
- Updates database statistics
- Performs database dumps
Ships transaction logs to another
server (SQL Server Enterprise Edition)
To use the wizard:
Open Enterprise Manager.
From the Tools menu, select Database
Click Next and then select the
databases that you want to create a plan for. You can select more than one database,
as shown in Figure A.
Click Next, and you will advance to the
screen shown in Figure B, which provides options for optimizing the
performance of your database. For example, the ‘Reorganize data and index pages’
option drops and re-creates all indexes in the database.
Click Next to proceed to the Database
Integrity Check screen, shown in Figure C. Integrity checks examine
allocation and structural integrity of the tables and indexes in your database.
It is recommended that you run these checks prior to backing up your database.
Click Next to display the Specify the
Database Backup Plan screen. As you can see in Figure D, this screen
allows you to create an automated backup as part of your maintenance plan.
Click Next to display the screen shown
in Figure E. Here, you can specify further parameters for your backup,
such as where to store your backup file.
Click Next. If you are using
transaction-log backups, you can use the options in this screen to specify
parameters similar to those shown in Figure E.
Click Next to display the Reports to
Generate screen, shown in Figure F, which allows you to create a report about
your maintenance plan. You can specify where the report is saved and how long
before it is deleted. If you have configured SQL Mail, you can have SQL Server
email the report to you.
10. Click Next, and you’ll see the Maintenance Plan History
screen, shown in Figure G. Here, you can set up SQL Server to write your
history to the history table on the local server and/or a remote server.
11. Click Next, to bring up the final screen in the Database
Maintenance Plan Wizard. Provide a name for your plan, review your options, and
In the figures above, we created
plans for Northwind and Pubs, but if you are creating plans for the system
databases, you need to select All System Databases in the wizard. Follow the
same steps as above but do not select the Automatically Repair Any Minor
Problems option; this will always fail on system databases.
Testing the your plan
Once the job is complete, you can view the job by expanding the SQL Server
Agent and clicking on the Jobs folder, as shown in Figure H. To test the
job, run it manually by right-clicking on the job and selecting Start Job. Once
the job is kicked off manually, the Status changes to Executing, as shown in Figure
I. When the job is complete, the Status field changes to Not Running. (If
you keep the window open and wait for the job to finish, you will have to
right-click and hit Refresh every few minutes.)
Now, right-click on the database
and choose View Job History for details on when it completed or failed, as
shown in Figure J. If you need to change any of your information, you
can edit the job by right-clicking on it and choosing Properties to display the
options shown in Figure K.
Click for larger image
In the General tab, you can change
the category and the name of the maintenance plan. Clicking on the Steps tab
displays the options shown in Figure L, which enable you to add or
delete tasks. In the Schedules and Notifications tabs, you can modify the
schedule of the maintenance plan and change who is notified when the job is
You now should be well on your way
to creating maintenance plans. By automating these important tasks with the
Maintenance Plan Wizard, you will keep your database running like a brand new