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

book

Article ID: 193109

calendar_today

Updated On:

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 ?
 

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.
 
 

Environment

Client Automation - All Versions
 

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)
 

Attachments

1592314694084__Clean_mdb.zip get_app