In an earlier BladeLogic Server Automation (BSA) Pulse Blog, we looked at how to avoid database referential integrity errors when running the BSA database cleanup commands. This post will look at how another issue sometimes encountered during BSA database cleanup, table-locking and the job hangs it can cause, can be avoided with some recent enhancements to the list of BSA database indexes.



Problem Summary


It is generally advisable to try to schedule the BSA database cleanup commands to run during off-peak hours as they are database-intensive tasks and have the potential to affect the performance of other BSA jobs running at the same time. However, in a real-world environment, it is, of course, seldom possible for the cleanup jobs to run completely in isolation and not overlap with other BSA jobs. While a slight hit on the performance of other jobs during the execution of BSA DB Cleanup may be acceptable, what we have occasionally encountered in the past is BSA jobs effectively becoming hung/blocked while the BSA database cleanup commands are running. The jobs may wait in the "Waiting to Run" state or they may reach a certain point in their execution before seeming to pause with no obvious progress being displayed in the "Tasks in Progress" pane of the BSA console. Since the cleanup commands may take hours to complete, a restart of the BSA Application Servers is often required to resolve this issue and allow the jobs to execute as normal. The problem may also present itself as hung login attempts to the BSA console.


Root Cause

In most cases, the BSA database cleanup will first delete data from the child tables and then from the parent tables. In certain cases, where the foreign key definition has been changed to "on-delete cascade" in order to avoid foreign key violations, the BSA database cleanup commands will trigger cascading deletes which means that if a record in the parent table is deleted, then the corresponding records in the child table will also automatically be deleted.


If the foreign key on the child table is not indexed, undesirable and expensive table scans may occur on the child table which can cause lengthy table-level locking on the parent table. This topic is explained very well in the following Oracle and SQL Server articles:




Similar to the solution for the Referential Integrity errors, the solution here is to run a SQL script on the BSA DB/schema to create new indexes on the foreign keys which the BSA Development team have deemed to be appropriate. As discussed in the above articles, not every foreign key will have an index added. The SQL script to run depends on your version of BSA and whether you are running Oracle or SQL Server.



Obtaining the Solution


BSA 8.2.X:

8.2.X Oracle:

8.2.X SQL Server:


BSA 8.3.X:

8.3.X Oracle:

8.3.X SQL Server:


*BSA 8.5.X:

8.5 Oracle:

8.5 SQL Server:


*Note - Although many of the new indexes were added "out of the box" in BSA 8.5.1, we do update the scripts occasionally and add new indexes so it is still a good idea to run the latest 8.5.X version of the script even if running BSA 8.5.1.

The above .rar files are up-to-date as of 10/21/2014 and will be updated should the BSA development team add any new indexes in the future.


Each .rar file contains the SQL Script to be run and a readme.txt file. The Add Index SQL scripts do not require the BSA Application Servers to be down while they are running though they can take over an hour to complete depending on the size of the environment.


Once the appropriate script has been executed on the BSA DB/Schema, the BSA database cleanup commands should be rerun and monitored. In many cases, a performance improvement may be observed due to the addition of the indexes and the likelihood of table-locking resulting in BSA jobs being blocked should be greatly reduced. If BSA job hangs are still observed during the execution of the cleanup commands, further analysis will be required and the following should be captured and provided when opening a ticket with BMC Support:


Oracle DB:

  1. BSA Job Run Log for the Cleanup NSH Script Job.
  2. BSA Job Run Logs for the hung jobs.
  3. Export of Appserver Details from the BSA UI at the time of the hang. (Configuration - Infrastructure Management - Export Detail Report)
  4. Oracle AWR, ADDM and ASH reports for the timeframe from when cleanup was kicked off until the job hangs were observed. These can be captured after the appservers have been restarted and do not need to be captured at the time of the hang. For AWR reports, it is preferable to gather hourly reports for the entire duration of the problem rather than a single report for the entire duration.
  5. Output of the appropriate attached SQL Query run as the system or sys user. These must be run at the time of the hang:
    • GetOracleLocks.txt for non-RAC environments
    • GetOracleRACLocks.txt for Oracle RAC environments.

SQL Server DB

  1. BSA Job Run Log for the Cleanup NSH Script Job.
  2. BSA Job Run Logs for the hung jobs.
  3. Export of Appserver Details from the BSA UI at the time of the hang. (Configuration - Infrastructure Management - Export Detail Report)
  4. The output of the following SQL Query run on the BladeLogic DB. These must be run at the time of the hang:
    • sp_whoisactive @get_locks = 1
    • Note: The sp_whoisactive procedure may need to be installed by the DBA
  5. From Microsoft SQL Server Management Studio, click on "Activity Monitor" from the toolbar. Click on the "Recent Expensive Queries" Panel.  Copy the top 5 longest executing queries from the grid and paste to a file.


Additional recommendations for avoiding blocking during cleanup:


  1. In Oracle environments, ensure that the BMC-recommended gather_schema_stats routine is being run frequently in place of the Oracle out of the box procedure. See KA288648 for details.
  2. When performing the initial run of BSA database cleanup, the cleanupHistoricalData commands should all be run to completion before the cleanupDatabase or hardDeleteAllSharedObjects commands are attempted. The cleanupHistoricalData commands are db-side stored procedures and are better-equipped to handle very large data loads. Allowing the cleanupHistoricalData commands to reduce the size of the tables as much as possible first will greatly reduce the workload to be performed by cleanupDatabase and hardDeleteAllSharedObjects.
  3. In BSA 8.3.2, 8.3.3 and 8.5.X, an offline database cleanup utility was added to the product. As the name suggests, this utility requires the BSA Application Servers to be down but it is able to perform very efficient cleanup on very large tables which may take the regular online cleanup many iterations to get through. If running cleanup for the first time on a large environment, or the first time after a lengthy gap, consider first running the offline database cleanup utility to efficiently reduce the size of some of the largest tables.

    BSA 8.3.2 documentation for the offline Database Maintenance Wizard
    BSA 8.3.3 documentation for the offline Database Maintenance Wizard
    BSA 8.5 documentation for the offline Database Maintenance Wizard
  4. The Cleaning up the BMC Server Automation database BMC Contributor documentation page is a very useful source for various BSA db cleanup tips and trips and is updated frequently.

I hope this post provides the information you need to help prevent db table-locking issues during the BSA DB cleanup commands. If you have any questions or comments on this topic, please feel free to use the comments section below. Also, please take a moment to rate the article so we can continue to monitor which topics and formats users find most useful.

To see more like this, see BMC BladeLogic Pulse Blog Posts

Join the Customer Support Community and give us feedback if our efforts in Communities are helpful, and how we can better serve you.
Connect with BMC Support Team over Chat sessions