Using Db Maint, Working with…

Jobs

The Job Definition dialog is used to create new jobs and to modify existing jobs. Note that not all options are available on SQL Server 6.5. To get a description for each parameter, position the mouse pointer over the option and a ToolTip will be displayed.

Job Name

This is read-only for an existing job. An existing job can only be renamed in the left pane of the main window.

For a new job, enter the job name here.

Database

Choose the database that you want to include in the job. You can also choose a Database Group, which will include all databases in the group for that job or <All>, which means that all databases (enumerated each time the job is executed) will be included in the job.

Backup tab

Specify whether to perform database, differential and log backup etc.

Save copy of backup file

Check this is you want Db Maint to save an extra copy of the backup file. The path to the file is specifies in the task definition.

Archive copy

Db Maint will take an archive copy of the backup file every first day of the selected period. This will only be performed if a database backup is scheduled that day (you probably only want to use this if you have scheduled database backup every day). The filename will be generated with the following pattern: dbname_yyyymmdd_save_dbas. These backup files will not be deleted automatically by Db Maint.

Perform test restore

Select this if you want Db Maint to restore the backup after the backup operation. You specify the prefix to be added to the database name and the path for the test restore database files in the Server properties dialog.

Log shipping

Db Maint can restore the database backups onto another machine (on which SQL Server need to be installed). The Db Maint server will have the source SQL Server connect to the remote server using xp_cmdshell and OSQL with a Windows login (so the service account for the local SQL Server service need to be added as a Windows login on the remote SQL Server). The paths specified for the database files are local paths seen from the remote server’s perspective.

Data verification tab

Specify the different database integrity checks to be performed. See SQL Server Books Online for details about each DBCC command. Which options that are enabled depend on your SQL Server version.

It is advisable to run as a complete consistency check as your time-window allows. This is so that you can catch possible inconsistencies as early as possible, and act accordingly.

Data Linkage

DBCC CHECKDB, which checks that data and index pages are correctly linked.

Include indexes

Check this if you want the DBCC CHECKDB command to also check index linkage. If you have a tight time window, you can define a task to do index checking only once a week, for instance.

Only physical structure

This executes the DBCC CHECKDB command with the PHYSICAL_ONLY option. This can be valuable for larger databases where it takes too long time to do a full check. Most hardware problems are discovered using the PHYSICAL_ONLY option.

Data allocation:

DBCC NEWALLOC (6.5) or CHECKALLOC (7.0 or later), which checks data and index pages against corresponding extent structures.

Include indexes:

Check this if you want the DBCC NEWALLOC/CHECKALLOC command to also check index linkage. If you have a tight time window, you can define, for instance, a task to do index checking only once a week.

Text/Image allocation

DBCC TEXTALL, which checks the allocation of text and image columns.

System Tables

DBCC CHECKCATALOG, which checks consistency in the system tables.

Note:

Db Maint disables checkboxes that represent statements that are already covered. In SQL Server 7.0 or higher, for instance, you do not need to run CHECKALLOC if you run CHECKDB.

Optimizations tab

Reorganize data, rebuilding entire indexes in one step

Runs DBCC DBREINDEX. Rebuilds all indexes for all tables in the database. This makes the leaf level of the index tree more contiguous (reduced fragmentation).

Maintain original free space leaves the same amount of space in the leaf level of each index tree as when the index was originally built.

Change free space to __ % will leave specified amount of free space in the leaf level of the index tree.

Reorganize data, making pages physically sorted in logical order

Runs DBCC INDEXDEFRAG. Shuffles pages for the indexes so the physical order is the same as the logical order. Does not require a table lock while the operation is performed.

Update index statistics

Runs UPDATE STATISTICS, which updates the distribution information for each index. SQL Server uses the distribution page to find an optimal execution path for your queries. This is not available if you selected DBCC DBREINDEX, since a side effect of executing DBCC DBREINDEX is to get updated distribution statistics.

Recalculate space usage

Runs DBCC UPDATEUSAGE, which updates information in the sysindexes table. This information is used by the various space usage reporting commands.

Pre- and post- processing tab

Specify if SQL Server is to execute any TSQL commands before and/or after the other steps.