SQL MDB Maintenance and Optimization

Products

• CA Client Automation - IT Client Manager
• CA Client Automation

Issue/Introduction

As Client Automation (ITCM)'s backbone of the product is its database, maintenance & optimization needs to run periodically to help improve overall performance.  

• MS SQL server tables should be optimized every time the database has been updated with significant data. 
• The DsmMsSqlOpt.bat script helps you perform this optimization.
• The script performs maintenance tasks such as defragmenting the index and updating statistics. 
• This script effects only tables that are owned by Unicenter DSM. 

Environment

Client Automation - 14. and above

Resolution

There is a SQL script that runs the maintenance and optimization to accomplish this.  The script can be found on installation media (DVD) at the following location:
\Maintenance\Windows\mssql\DsmMsSqlOpt.bat 
The DSM Script must be run locally on the computer where the MDB is installed. 
 

DsmMsSqlOpt Syntax: 
DsmMsSqlOpt.bat [-pagecount=n] [-maxfrag=m] [-usereindex] [ {local | ServerName} [MDBName] ]

It is good practice to rebuild indexes with a fragmentation degree over 30% as the first maintenance step because rebuilding indexes runs much faster than defragmentation.
To achieve this, you must launch the DsmMsSqlOpt script with the options - usereindex and maxfrag=30 

Ex: DsmMsSqlOpt.bat -maxfrag=30 -usereindex

After the initial step, all tables with a fragmentation over 10% should be defragmented. 
This can be done by calling the DsmMsSqlOpt script with the option -maxfrag=10 

Ex: DsmMsSqlOpt -maxfrag=10

Additional Information

Reminder:
Based on fair usage, this script should be run about once a week

Note:
The %temp% variable must be set to an appropriate working directory before launching the DsmMsSqlOpt.bat script. 

Additional Info:
There is a Proper Care and Feeding of your SQL MDB, guide that goes farther in depth of what you can do to keep your MDB running more efficiently.