> Documentation > CommCell Management > Tools and Utilities for Troubleshooting > DBMaintenance Utility

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

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>

  • -N <non-CommServeDatabase> (optional)

To run the utility on a cluster, specify the -S, -U and -P connection parameters. For any database other than the CommServe, use the -N connection parameter.

Maintenance Parameters

The following maintenance parameters are available:

  • full - Check the database for inconsistencies, shrink the database and reindex all tables at the end.

    Run a full maintenance for every six months. Before you perform a full maintenance, stop the CommServe services. For instructions about stopping the services, see Stopping a Service on Windows Clients.

    Note

    You can run DBMaintenance with CommServe services running, but there is a possibility that Commvault operations could fail when indexes are large and heavily fragmented (due to database tables being locked during the maintenance process). You can reduce the likelihood of failed operations if you run DBMaitenance at a time when there is less activity in your environment. If you consistently see lock timeouts in the Commvault 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 once in every 2 weeks to reduce index fragmentation and minimize deadlocks. Do not stop the CommServe services.

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.

Notes

  • 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 for the next 28 days and logs diagnostic information in the Troubleshooting database.

  • startlogfilegrowth - Identifies the queries that fill up the CommServ database log file and logs diagnostic information in the CommServ database. This parameter continues to log diagnostic information until the SQL service is restarted.

What to Do Next

You are recommended to take a disaster recovery backup of the CommServe database. For instructions, see Performing a Disaster Recovery (DR) Backup.

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

Loading...