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

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 - 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 2.2 (19 June 2025)

Version History:
- Version 2.0 (10 October 2024) : Add cleanup in usd_applic table for SW Delivery and SW Removal jobs for computers which have no more the scalability server role.
- Version 2.1 (19 May 2025) : Remove lock "Eval Update" for computers which are no more part of a "Target Evaluation" task.
- Version 2.2 (19 June 2025) : Delete in usd_applic duplicate SD Jobs. One job is in status OK and one job is in status Job Execution Ordered or Job Execution Staged