Working with the SQL Server 2000 Maintenance Plan Wizard
August 29, 2005
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
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:
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. 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.
5. 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.
6. 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.
7. 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.
8. 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.
9. 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 click Finish.
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 completed.
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 machine.