Introduction to Databases and Microsoft Access 7/8

Organising Database Maintenance with MS SQL Server 7.0
How to use the Database Maintenance Plan Wizard
How to view and change jobs       

* What does Database Maintenance do?

Database Maintenance can be organised with the Database Maintenance Plan Wizard, which can automatically create a database maintenance plan on a per database basis. It has the ability to schedule the following jobs:

·        Rebuild database indexes

·        Run the UPDATE STATISTICS command on the database

·        Check the allocation of text and image columns in the database

·        Schedule database backups

·        Schedule transaction log backups

·        Generate a report showing what the plan successfully and unsuccessfully accomplished.

Using the Database Maintenance Plan Wizard is a simplification of database administration tasks, and is therefore limiting in its scope. For further flexibility, the only option is to set up maintenance tasks manually using the SQL Server Agent.

How to use the Database Maintenance Plan Wizard:

1.    Start the SQL Enterprise Manager, and select the SQL Server you want to currently work with.

2.    From the Tools menu, select Database Maintenance Planner.

This displays the Database Maintenance Plan Wizard Welcome Screen.

3.    Read the screen, and when done, click Next. This displays the Select Databases screen.

4.    Here, you must select which database or databases you want to create a maintenance plan for. You can select: all databases, all system databases, all user databases, or you can select any combination of databases. Click on the radio button of your choice and then click on Next.

5.    This displays the Update Data Optimisation Information screen.
a) The Reorganise Data and Index Pages option is used to determine whether you want to rebuild all the indexes in the database.

The Reorganise Pages with the Original Amount of Free Space option refers to rebuilding indexes using the same fillfactor as was originally used when the indexes were created. fillfactor refers to the amount of empty space in a newly created index. This empty space is used to store index information when new records are added to a table.
High fillfactor => reindexing is required less often
Low fillfactor => less space is taken up by a table’s index
Whatever number you enter in the Change Free Space per page percentage field, the fillfactor will be its inverse. For example, if you enter 10 in the box, the fillfactor will be 90.
b) The next option decides whether you want to run the update statistics command on the tables in the database, which speeds up queries.

Note that rebuilding indexes automatically accomplishes this goal. If you choose the Update the Statistics Used By the Query Optimiser option, you also have the option to choose the Sample % of the database.
High Sample % => More accurate results
Low Sample % => Less time taken to run
c) The textbox for the When It Grows Beyond option determines at what point SQL Server should reclaim space.

The default value of 50 MB means that whenever the used space in a database exceeds 50 MB, SQL Server should reclaim unused space in the database. Note that some empty space is required to re-create indexes. There is a default value of 10% in the Amount of Free Space to Remain After Shrink.
d) Lastly, at the bottom of the window, specify how often you want to perform the tasks listed on this page of the wizard. The default is once a week on Sunday at 1:00 AM.

6.    The next screen is the Database Integrity Check.
a) In the first option, you determine whether or not any integrity checks are run on the database; these can find out if there are any problems with the database and maybe even fix minor problems. If the database is large and the check takes too much time, you can exclude the indexes in the check.

b) Next, you determine when these checks are run, in relation to when backups are performed. Almost always you will want to choose to perform these tests before doing a backup.
c) Lastly, you determine when the tasks on the screen will run.

7.    The Specify the Database Backup Plan screen is displayed.

a) The first option is used to determine whether or not you want to perform a complete backup of your database.
b) Next, determine whether or not you want the integrity of the database to be checked after the backup is complete. This is recommended, assuming you have the time to run the test.
c) The next option determines where the back will be made (tape or disk).
d) Once again, specify the desired schedule.

8.    Assuming you chose to backup to disk, the Specify Backup Disk Directory screen appears.

a) Either choose the default SQL Server backup folder, or specify your own (recommended), with the Use this directory option.
b) The next option for subfolders is up to the user.
c) The next option determines how long your disk backups will be kept. The default is four weeks.
d) Lastly indicate what file extension you want to use for your disk backups. The default is BAK (recommended).

9.    Next, the Specify the Transaction Log Backup Plan screen is displayed. Is almost identical to the Specify the Database Backup Plan screen in step 7, and the options are the same.

10.The Specify Transaction Log Backup Disk Directory screen appears. This screen is also nearly identical to the Specify Backup Disk Directory screen from step 7.

11.In the Reports to Generate screen, after a maintenance plan has run, you can have a report automatically generated letting you know whether each step in the plan was completed successfully or unsuccessfully.

12.In the Maintenance History screen,

a) The first option allows you to choose whether you want to maintain a permanent record of every time a maintenance plan runs in the MSDB database of the local server. By default, every plan will be stored forever, unless you limit the number of rows by selecting the Limit Rows in the Table To option.
b) In the Remote server section, if you have more than one server, you can use this option to force all the SQL Servers to write all the history to the same SQL Server.

13.And finally, you are shown the final screen of the wizard.

How to view and change jobs:

After one or more jobs have been created by the Database Maintenance Plan Wizard, they can be viewed and changed at any time.

1.    Load the SQL Enterprise Manager, and then select the SQL Server you want to work with.

2.    Choose the database in question.

3.    All available maintenance plans appear in the details pane in the Maintenance section.

4.    To view or change a maintenance plan, click on it. This displays the Maintenance Plan dialog box.


5.    From here, you can select the appropriate tab and then view or change any of the settings you originally selected when you first created the plan.

6.    When you are finished, click OK. This will save any changes you made to the plan. You will see a message telling you that the changes were made, and you will have to click OK to remove the message.