search cancel

Client Automation - How to clean SD tables from lost rows or data inconsistency ?

book

Article ID: 109875

calendar_today

Updated On:

Products

CA Client Automation CA Client Automation - IT Client Manager CA Client Automation - Software Delivery

Issue/Introduction

On DOMAIN when a SD process crash or is killed during SD job operations, some rows in SD tables could be lost (with missing links) or inconsistent. 
This could generate some SD problems and/or performance problems.
 
 
Example of possible errors :
 
- When trying to delete a package following error appears :
SDM000126 - Software cannot be removed/archived because of an ongoing job and/or is included in a template group.

But package is not in ongoing SD job or in Software Policies.


- Some SD Jobs remain in GUI with executing status but SD Job Container has been deleted.
- Build of SD Job Container failed.
 
 
 
How to clean the SD tables from incorrect rows ?
 

Environment

Client Automation 14.0 all versions.

Resolution

1- Copy the attached files check_sd_tables_REPAIR.sql, check_sd_tables_VIEW.sql and check_sd_tables.bat under DSM\bin directory (ex: C:\Program Files (x86)\CA\DSM\Bin) on Domain Manager

 

2- Open a command prompt with administrator privileges and execute in REPAIR mode:

cd /D "C:\Program Files (x86)\CA\DSM\Bin"

check_sd_tables.bat REPAIR


 

3- Check the log C:\Program Files (x86)\CA\DSM\log\check_sd_tables.log if it has been executed successfully.

 

Remarks :

  • The script uses logged user to connect on SQL Server. 

If logged user has not enough right on SQL Server, do this :

Edit file check_sd_tables.bat and put valid SQL credentials in these lines: 

SET SQLUSER=
SET SQLPWD=

 

Example

SET SQLUSER=ca_itrm
SET SQLPWD=NOT_changedR11

 

 

  • To execute the script in VIEW mode (no update/delete in database) to check if there are some corrupted data, execute the script without REPAIR parameter :

    cd /D "C:\Program Files (x86)\CA\DSM\Bin"
    check_sd_tables.bat



 

Additional Information

Latest version 1.8 (14 September 2022)

Attachments

1664289890535__check_sd_tables_v18.zip get_app