DBMaintenance Utility

Using DBMaintenance Utility

• Connection Parameters
• Maintenance Parameters
• Custom Maintenance Parameters
• Troubleshooting Parameters

Recommended Usage

Examples

Overview

DBMaintenance utility can be used to perform the following maintenance tasks on the CommServe database:

• Check the database for inconsistencies
• Re-index all database tables
• Shrink the database

Using DBMaintenance Utility

This utility is located at <Software Installation Path>/Base directory. From the command prompt, run dbmaintenance with appropriate parameters from the list of available parameters. Running the utility without any parameters will give the complete list of supported parameters.

The CommCell services must be stopped before performing database maintenance.

Connection Parameters

The database instance and the logon credentials can be provided in the following optional connection parameters:

• -S <DBServer> -
• -U <username>
• -P <password>

If this utility is run on a CommServe without any of the above mentioned connection parameters, the database instance will be automatically detected, and the logon information will be retrieved from the encrypted data in the registry.

Maintenance Parameters

The following maintenance parameters are available:

• full - Check the database for inconsistencies and reindex all tables and shrink the database.
• recommended - Check the database for inconsistencies and reindex the tables which are the largest and most often accessed/modified. A database shrink is not performed.

Custom Maintenance Parameters

Instead of the two maintenance schedules outlined above (full and recommended), it is also possible to specify the type of maintenance from the custom maintenance parameters given below.

The following custom maintenance parameters are available:

• checkdb - Perform a database integrity check
• reindexall - Reindex all tables.
• reindex - Reindex largest and most often used tables (Recommended).
• reindex <table[,table,...]> - Reindex the specified table(s), separated by commas.
• shrinkdb - Shrink the database.

Shrinking the database is not recommended on a regular basis. For this reason do not perform a full maintenance regularly. If you would like to reindex all tables, use the reindexall option. Combining the reindexall with the checkdb option will have desired effect.

Only perform a shrinkdb if a lot of free space has become available, or it has been a long time since the last maintenance. Please note that database maintenance can take half an hour or even longer to execute depending on database size.

Troubleshooting Parameters

The following options can be used to troubleshoot the database:

• startdblockdetect - Starts a job to detect queries causing lock timeouts/deadlocks.
• stopdblockdetect - Stops a lock detect job.
• dblockdetectstatus - Lists the number of locks that were detected.

Recommended Usage

Review the following recommendations before using this utility:

• When this utility is run for the very first time, it is recommended to perform a full database maintenance.
• After the first use, the following maintenance schedule is recommended:
  • Perform a full maintenance for every 6 months.
  • Perform a recommended maintenance for every 4-6 weeks.

Examples

The following examples explain the usage of this command in different scenarios:

• Run the following from the command line to perform a full database maintenance.

  dbmaintenance -full

• Run the following from the command line to perform the recommended database maintenance.

  dbmaintenance -recommended

• Run the following from the command line to reindex just the archchunkmapping table.

  dbmaintenance -reindex archchunkmapping