Loading...

Performing CommServe Database Maintenance Using the DBMaintenance Utility

Review the following recommendations before using this utility to perform CommServe database maintenance:

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

Before You Begin

  • Suspend all jobs. For instructions on suspending jobs, see Suspending a Job.
  • At the CommServe level, on the Activity Control tab, clear the Enable Scheduler check box to disable the scheduler. For instructions on disabling the scheduler, see Enabling or Disabling CommCell Activities.

Running the Utility

The DBMaintenance utility is located in the software_installation_path/Base folder. From the command prompt, run dbmaintenance with the appropriate parameters.

To automatically detect the database instance and retrieve the logon information from the encrypted data in the registry, run this utility on a CommServe database without any connection parameters.

To provide the database instance and the logon credentials, run this utility with the following connection parameters:

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

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: You can run DBMaintenance with CommServe services running, but there is a possibility of this interfering with SnapProtect operations when indexes are huge and heavily fragmented. If you consistently see lock timeouts in the SnapProtect logs, run DBMaintenance with the 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