DBMaintenance Utility

Table of Contents

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

Recommended Usage

Review the following recommendations before using this utility:

  • Use the utility during periods of low CommServe activity to minimize the effect on data protection operations.
  • When this utility is run for the very first time, use the full parameter to perform database maintenance.
  • After the first use, use the full parameter to perform database maintenance every six months.

Using DBMaintenance Utility

This utility is located in the 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.

Before You Begin

Suspend all jobs and disable the scheduler. For information on suspending jobs, see Job Controller - Advanced - Suspending a Job. For information on disabling the scheduler, see Activity Control - Advanced - Enabling/Disabling Operations.

Connection Parameters

Provide the database instance and the logon credentials 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.

    A full maintenance should only be done if DBMaintenance utility has not been run for several months. It is recommended to perform a full maintenance when the CommServe is not very busy or services can be shut down.

    Note: It is ok to run DBMaintenance with CommServe services running. But, there is a possibility of this interfering with Simpana operations when indexes are huge and heavily fragmented. If you consistently see lock timeouts in Simpana logs, run DBMaintenance with services stopped.

  • 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.

    Run the recommended maintenance regularly to reduce index fragmentation and minimize deadlocks. CommServe services do not need to be stopped.

When you are performing recommended database maintenance, the reindexing is performed based on the fragmentation level of a table.

  • If the fragmentation level is between 5% to 30%, the table is reorganized instead of reindexing them. The reorganization operation is faster than the reindexing.
  • If the fragmentation level is greater than 30%, the table is reindexed.

Custom Maintenance Parameters

Instead of the two maintenance schedules outlined above (full and recommended), it is 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.
  • reindexrecommended - Reindex largest and most often used tables (Recommended).
  • reindex <table[,table,...]> - Reindex the specified tables, separated by commas.
  • shrinkdb - Shrink the database.

Shrinking the database is not recommended on a regular basis. For this reason only perform a full maintenance every six months. 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

Use the following options to troubleshoot the database:

  • startdblockdetect - Adds a SQL Server agent job that runs every 25 seconds to monitor database lock timeouts, and creates the Troubleshooting SQL database to capture which process has the lock, which process is trying to lock, which table/objects the processes are waiting for, the current query being executed, and the transaction being executed.

    Troubleshooting Database Details

    • The database has the following tables: GxSQLGroupInfo, GxSQLLockedObjects, and GxSQLSPIDInf.
    • The data file size is restricted to 2 GB.
    • The log file maximum size is 2048 GB. (If needed, you can restrict the maximum log file size per your requirements.)

    Note: Once the Troubleshooting database reaches its maximum size, no more entries are added to the database. You must manually drop the database and re-enable the startdblockdetect option to recreate it.

  • stopdblockdetect - Stops a lock detect job.
  • startlogtempdbgrowth - Identifies the queries that fill up tempdb and logs diagnostic information in the Troubleshooting database.
  • startlogfilegrowth - Identifies the queries that fill up CommServ database log file and logs diagnostic information in the CommServ database.

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