Managing a database server is like owning a car: You need a maintenance plan to keep things running efficiently and to preempt problems down the road. Just as your car requires periodic tune-ups, your database needs regular attention to ensure peak performance. To help you keep your database humming, I’m going to show you how to create a maintenance plan for Microsoft 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 following tasks:

  • Runs database integrity checks
  • Updates database statistics
  • Performs database dumps
  • Ships transaction logs to another server (SQL Server Enterprise Edition)

To use the wizard:

  1. Open Enterprise Manager.
  2. From the Tools menu, select Database Maintenance Planner.
  3. 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.
  4. Figure A

  5. Click Next, and you’ll 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.
  6. Figure B

  7. 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.
  8. Figure C

  9. 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.
  10. Figure D

  11. 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.
  12. Figure E

  13. Click Next. If you’re using transaction-log backups, you can use the options in this screen to specify parameters similar to those shown in Figure E.
    1. 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 e-mail the report to you.
    2. Figure F

    3. 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.
    4. Figure G

    5. Click Next to bring up the final screen in the Database Maintenance Plan Wizard. Provide a name for your plan, review your options, and click Finish.


    In the figures above, we created plans for Northwind and Pubs, but if you’re 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 maintenance 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.

    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.)

    Figure I

    Now, right-click on the database and choose View Job History for details on when it completed or failed, as shown in Figure J.

    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.

    Figure K

    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 gets notified when the job is completed.

    Figure L

    In this article, we discussed the importance of a database maintenance plan and explained how to create one in Microsoft SQL Server 2000 to save time and keep your databases running efficiently. By automating these important tasks with the Maintenance Plan Wizard, you will keep your database running like a brand new car.

    Have a comment or a question?

    We look forward to getting your input and hearing about your experiences regarding this topic. Post a comment or a question about this article.