How to run MS SQL Server Database Maintenance (DsmMsSqlOpt.bat)

Document ID : KB000024846
Last Modified Date : 14/02/2018
Show Technical Document Details

Introduction:

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 CA Client Automation.

 

Environment:                                          

CA Client Automation - All Versions

 

Instructions:

The script can be found on installation media (DVD) at the following location - Maintenance\Windows\mssql\DsmMsSqlOpt.bat

OR

On the server at %Program Files%\CA\DSM\database\mdb_install\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] ]

Important Notes:

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

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