Client Automation - Huge size of mdb database. How to reduce its size ?

Products

• CA Client Automation - Asset Management
• CA Client Automation - IT Client Manager
• CA Client Automation

Issue/Introduction

Size of mdb.mdf file is huge and it is still growing every weeks.

Why and How to reduce its size ?

 

Environment

Client Automation - All Versions

 

Cause

Huge size of mdb could be caused by scheduled reports which are saving result of reports in database.

If a lot of reports are scheduled to be executed every days or weeks after while the size of all report result tables could be huge.

 

 

Resolution

1- Copy the 2 files CLEAN_MDB.SQL and clean_mdb.bat attached below on Domain Manager or SQL Server machine in a temp directory (eg: c:\temp)

This script will delete result of reports older than 90 days

 

 

2- Open a cmd prompt with Administrative privileges and execute this :

 

cd /D c:\temp

clean_mdb.bat REPORT SQLServerName

 

or if it is in a named instance :

 

clean_mdb.bat REPORT SQLServerName\InstanceName

 

Or if connected user has not enough rights on SQL Server : 
 
clean_mdb.bat REPORT SQLServerName\InstanceName ca_itrm NOT_changedR11
 

Replace :

• SQLServerName by the computer name hosting SQL Server with mdb database
• InstanceName by the name of SQL Instance (if it is a named instance).
  
  
• NOT_changedR11 by correct password for ca_itrm if it has been changed.

 

3- This will release space inside the file mdb.mdf.

 

But to reduce the size of this file in Windows file system, following SQL statement should by executed with Microsoft SQL Server Management Studio
 
DBCC SHRINKFILE (mdb,20)