Select Servers

Use the Select Servers screen to select one or more servers containing databases for which you want to create a database maintenance plan. Rather than all the servers available on the network, only those servers to which the local MSX (master) server can send jobs are listed.

The (local) server is the MSX (master) server.

All other servers are TSX (target) servers.


Select Databases

Use the Select Databases screen to view or specify the following options.

Options

All databases

Generate a maintenance plan that runs maintenance tasks against all Microsoft® SQL Server™ databases.

All system databases (master, model, and msdb)

Generate a maintenance plan that runs maintenance tasks against each of the SQL Server system databases. No maintenance tasks are run against user-created databases.

All user databases (all databases other than master, model, and msdb)

Generate a maintenance plan that runs maintenance tasks against all user-created databases. No maintenance tasks are run against the SQL Server system databases.

These databases

Generate a maintenance plan that runs maintenance tasks against only those databases that are selected. At least one database in the list must be selected if this option is chosen.

Ship the transaction logs to other SQL Servers (log shipping)

Set up log shipping as part of this maintenance plan. For more information, see Log Shipping.

Note This feature is available only in the SQL Server 2000 Enterprise and Developer editions.


Update Data Optimization Information

Use the Update Data Optimization Information screen to view or specify the following options.

Options

Reorganize data and index pages

Cause the indexes on the tables in the database to be dropped and re-created with a new FILLFACTOR. The FILLFACTOR determines how much empty space to leave on each page in the index, thereby reserving a percentage of free space on each data page of the index to accommodate future expansion. As data is added to the table, the free space fills because the FILLFACTOR is not maintained. Reorganizing data and index pages can reestablish the free space.

Reorganize pages with the original amount of free space

Cause the indexes on the tables in the database to be dropped and re-created with the original FILLFACTOR that was specified when the indexes were created.

Change free space per page percentage to

Cause the indexes on the tables in the database to be dropped and re-created with a new automatically calculated FILLFACTOR, thereby reserving the specified amount of free space on the index pages. The higher the percentage, the more free space is reserved on the index pages and the larger the index grows. Valid values are from 0 through 100.

Update statistics used by query optimizer.

Cause the distribution statistics of each index created on user tables in the database to be resampled. The distribution statistics are used by Microsoft® SQL Server™ to optimize navigation through tables during the processing of Transact-SQL statements. To build the distribution statistics automatically, SQL Server periodically samples a percentage of the data in the corresponding table for each index. This percentage is based on the number of rows in the table and the frequency of data modification. Use this option to perform an additional sampling using the specified percentage of data in the tables.

Sample % of the database

Generate distribution statistics by sampling the percentage of data in the tables. The higher the percentage, the more accurate the statistics, but the longer the sampling takes. If the specified value does not generate a sufficient sample, SQL Server determines an adequate sample size automatically. Valid values range from 1 through 100.

Remove unused space from database files

Remove any unused space from the database, thereby allowing the size of the data files to be reduced.

When it grows beyond

Remove unused space from the database only if the database exceeds the specified size, in megabytes (MB).

Amount of free space to remain after shrink

Determine the amount of unused space to remain in the database after the database is shrunk (the larger the percentage, the less the database can shrink). The value is based on the percentage of the actual data in the database. For example, a 100 MB database containing 60 MB of data and 40 MB of free space, with a free space percentage of 50 percent, would result in 60 MB of data and 30 MB of free space (because 50 percent of 60 MB is 30 MB). Only excess space in the database is eliminated. Valid values are from 0 through 100.

Schedule

Set the frequency that the data optimization tasks (scheduled using SQL Server Agent) are executed. The default is every Sunday at 1:00 AM.

Change

Change the default schedule.


Database Integrity Check

Use the Database Integrity Check screen to view or specify the following options.

Options

Check database integrity

Check the allocation and structural integrity of user and system tables, and indexes in the database, by running the DBCC CHECKDB Transact-SQL statement. This ensures that any integrity problems with the database are reported, thereby allowing them to be addressed later by a system administrator or database owner.

Include indexes

Check the data and index pages in the database during the integrity tests.

Attempt to repair any minor problems

Attempt to correct any minor problems detected during the database integrity tests automatically. When this option is selected, the database will be put in single user mode each time the maintenance plan runs. It is recommended that this option be selected.

Exclude indexes

Check only the data pages in the database during integrity tests. This does not check indexes. This option executes faster than clicking Include indexes because fewer pages in the database are checked.

Perform these tests before doing backups

Cause the database and/or internal data integrity tests to be executed before backing up the database or transaction log. If the integrity tests detect inconsistencies, any subsequent database or transaction log backup is not backed up.

Schedule

Set the frequency that the data integrity tasks (scheduled using SQL Server Agent) are executed. The default is every Sunday at 12:00 midnight.

Change

Change the default schedule.


Specify the Database Backup Plan

Use the Specify the Database Backup Plan screen to view or specify the following options.

Options

Back up the database as part of the maintenance plan

Cause the entire database to be backed up as part of the maintenance tasks. Backing up the database is important in case of system or hardware failure (or user errors) that cause the database to be damaged in some way, thus requiring a backed-up copy to be restored.

Verify the integrity of the backup on completion of the backup

Check that the backup set is complete and all volumes are accessible by executing the RESTORE VERIFYONLY Transact-SQL statement.

Tape

Back up the database to the specified tape device. Only tape devices attached to the computer containing the database are available.

Disk

Back up the database to disk. For more information about how to specify the location of the database backup, see Specify Backup Disk Directory.

Schedule

Set the frequency that the database backup tasks (scheduled using SQL Server Agent) are executed. The default is every Sunday at 2:00 AM.

Change

Change the default schedule.


Specify Backup Disk Directory

Use the Specify Backup Disk Directory screen to view or specify the following options.

Options

Use the default backup directory

Back up the database to the default backup disk directory located on the computer that contains this database. This is the \MSSQL\BACKUP directory for default instances of Microsoft® SQL Server™ 2000, and the \MSSQL$instancename\BACKUP directory for named instances of SQL Server 2000.

Use this directory

Back up the database to the specified disk directory. Only disks located on the same computer as the database can be used. Click the browse (...) button to change the default directory used to back up the database. Only drives on the computer containing the SQL Server database being backed up can be selected.

Create a subdirectory for each database

Create a subdirectory under the specified disk directory containing the database backup for each database that is being backed up as part of the maintenance plan.

Remove files older than

Delete database backups automatically that are older than the specified period. A history of database backups should be maintained in the event that the database must be restored to a point in time earlier than the last performed backup. Retain as many backups as disk space allows and as far in the past as necessary.

Backup file extension

Define the file extension used for each file that contains the database backup. The default file extension is .bak.

Note Disk backup file names are generated automatically (for example, pubs_tlog_199803120203.bak where 199803120203 is the timestamp).


Specify Transaction Log Backup Disk Directory

Use the Specify Transaction Log Backup Disk Directory screen to view or specify the following options.

Options

Use the default backup directory

Back up the transaction log to the default backup disk directory located on the computer that contains this transaction log. This is the \MSSQL\BACKUP directory for default instances of Microsoft® SQL Server™ 2000, and the \MSSQL$instancename\BACKUP directory for named instances of SQL Server 2000.

Use this directory

Back up the SQL Server transaction log to the specified disk directory that is located on the computer containing this transaction log. Click the browse (...) button to change the default disk directory used to back up the transaction log. Only drives on the computer containing the specified SQL Server transaction log can be selected.

Create a subdirectory for each database

Create a subdirectory under the specified disk directory for the transaction log backup for each database that is being backed up as part of the maintenance plan.

Remove files older than

Delete automatically any transaction log backups older than the specified period. A history of transaction log backups should be maintained in the event that the database needs to be restored to a specific point in time. Retain as many backups as disk space allows and as far in the past as necessary.

Backup file extension

Define the file extension for each file containing a transaction log backup. The default file extension is .trn.

Note Disk backup file names are generated automatically (for example, pubs_tlog_199803120203.bak where 199803120203 is the timestamp).


Specify Transaction Log Backup Plan

Use the Specify Transaction Log Backup Plan screen to view or specify the following options.

Options

Backup the transaction log as part of the maintenance plan

Cause the transaction log to be backed up as part of the maintenance plan. Backing up the transaction log is necessary in order to recover the database to the point of failure.

Verify the integrity of the backup when complete

Check that the backup is complete and all volumes are accessible by executing the RESTORE VERIFYONLY Transact-SQL statement.

Tape

Back up the database to the specified tape device. Only tape devices attached to the computer containing the database are available.

Disk

Back up the database to disk. For more information about how to specify the location of the database backup, see Specify Backup Disk Directory.

Schedule

Set the frequency that the transaction log backup tasks (scheduled using SQL Server Agent) are executed. The default is every Monday through Friday at 12:00 midnight.


Reports to Generate

Use the Reports to Generate screen to view or specify the following options.

Options

Write report to a text file in directory

Specify the full path and name of the text file into which the report is to be generated. The report contains details of the steps executed by the maintenance plan, including any error information. The report maintains version information by adding a date to the file name. The date is generated as a suffix to the file name but before the extension, in the form _YYYYMMDDHHMM. For example: "DB Maintenance Plan10_199804090838.txt".

Click the browse (...) button to change the default directory for the text file. Only directories on the computer running the maintenance plan can be selected.

Delete text report files older than

Delete text report files automatically that are older than the specified period. A history of text report files should be maintained so that you can check the maintenance tasks that have been executed in the past.

Send E-mail report to operator

Specify the operator to whom the generated report will be sent through SQL Mail. Click the browse (...) button to change the properties of the specified operator using SQL Server Enterprise Manager.

New Operator

Create a new operator using SQL Server Enterprise Manager.


Maintenance Plan History

Use the Maintenance Plan History screen to view or specify the following options.

Options

Write history to the msdb.dbo.sysdbmaintplan_history table on this server

Write the report as rows to this table on the server upon which the maintenance plan was executed. The report contains the steps executed by the maintenance plan, including database name, activity, date, result (success or failure), and any error information. It includes one row for each activity, per database, per execution date.

Limit rows in the table to

Specify the maximum number of rows in the table that represent history for this plan only. If the number of history rows in the table for this plan exceeds this value, older rows for this plan (representing the earliest recorded history) are deleted. Setting this value can prevent the table from becoming too large and filling the msdb database (if auto-grow is not permitted). The default is 10,000.

Write history to the server

Write the report as rows to the msdb.dbo.sysdbmaintplan_history table on a remote server. Windows Authentication is used to connect to the remote server. The report contains the steps executed by the maintenance plan, including database name, activity, date, result (success or failure), and any error information. It includes one row for each activity, per database, per execution date.

Click the browse (...) button to change the remote server to which the report is written. Only instances of Microsoft® SQL Server™ can be selected.

Limit rows in the table to

Specify the maximum number of rows in the table that represent history for this plan only. If the number of history rows in the table for this plan exceeds this value, older rows for this plan (representing the earliest recorded history) are deleted. Setting this value can prevent the table from becoming too large and filling the msdb database (if auto-grow is not permitted). The default is 10,000.


Specify the Transaction Log Share

Use the Specify the Transaction Log Share screen to specify a network share where you want the transaction log files to be created.

Options

Network share name for <backup directory>

Specify the network share name for the indicated backup directory. Click the browse (...) button to search for the desired network share.


Specify the Log Shipping Destinations

Use the Specify the Log Shipping Destinations screen to select a destination server for log shipping.

Options

Destination Server

View the server to which transaction logs will be shipped.

Database

View the name of the database setup for log shipping.

Add

Add a new log shipping destination.

Edit

Edit the highlighted log shipping destination.

Delete

Delete the highlighted log shipping destination.

See Also

Log Shipping


Add or Edit Destination Database

Use the Add or Edit Destination Database screen to enter information about the destination database for log shipping.

Options

Server name

Choose the destination server for log shipping.

Directory

Choose the directory on the destination server where you want the transaction logs to be shipped. Click the browse (...) button to search for the appropriate disk directory.

Create and Initialize New Database

Create a new database on the destination server for use with log shipping.

Database Name

Specify the name of the database you want to create.

For Data

Specify the directory on the destination server that you want to use for the new database files. Click the browse (...) button to search for the appropriate disk directory.

For Log

Specify the directory on the destination server where log files for the new database are to be stored. Click the browse (...) button to search for the appropriate disk directory.

Use Existing Database (No initialization)

Use an existing database on the destination server for log shipping.

Database Name

Choose the database to be used for log shipping.

No Recovery Mode

Leave the destination database in No Recovery mode. User access is not possible with databases in No Recovery mode.

Standby Mode

Leave the destination database in Standby mode.

Terminate users in database (Recommended)

Specify if the connections users have to the destination database should be automatically terminated.

Allow database to assume primary role

Specify if the destination server should be setup to be able to function as a primary server.

Directory

Specify the directory on the destination server where the database transaction logs are to be stored when the secondary server is functioning as the primary server.

See Also

Log Shipping


Initialize the Destination Databases

Use the Initialize the Destination Databases screen to specify whether to take a full database backup or to use the most recent backup file.

Options

Perform a full database backup now

Create a new full database backup for use by log shipping.

Use most recent backup file

Use the most recent backup file as specified in the text box. Click the browse (...) button to search for the most recent backup file. If the file is not located in the log shipping share, it will be copied to this location.

See Also

Log Shipping


Log Shipping Schedules

Use the Log Shipping Schedules screen to specify a schedule for any log shipping actions.

Options

Backup Schedule

View the current log backup schedule.

Change

Display the Edit Recurring Job Schedule dialog box, where you can change the current backup schedule.

Copy/Load Frequency

Set the frequency with which transaction log backups are to be loaded.

Load Delay

Set the age a transaction log backup must reach before it is eligible to be loaded.

File Retention Period

Set the age at which a transaction log backup will be deleted.

See Also

Log Shipping


Log Shipping Thresholds

Use the Log Shipping Thresholds screen to specify the log shipping thresholds. When these thresholds are exceeded, alerts are generated.

Options

Backup Alert Threshold

Set the maximum time between transaction log backups on the source server. If this time is exceeded, an alert will be generated.

Out of Sync Alert Threshold

Set the maximum time between the last transaction log backup on the source server and the last transaction log load on the destination server. If this time is exceeded, an alert will be generated.

See Also

Log Shipping


Specify the Log Shipping Monitor Server Information

Use the Specify the Log Shipping Monitor Server Information screen to specify name and authentication information for the central server, also called the Monitor Server, from which log shipping will be monitored.

Options

SQL Server

Select the instance of Microsoft® SQL Server™ from which log shipping will be monitored.

Use Windows Authentication

Use Windows Authentication when connecting to the log shipping monitor server.

Use SQL Server Authentication

Use SQL Server Authentication when connecting to the log shipping monitor server.

Login Name

Specify the login name to use for connecting to the log shipping monitor server.

Password

Specify the password to use for connecting to the log shipping monitor server.

See Also

Log Shipping