BSA/TSSA: Jobs are intermittently hanging especially under higher parallelism in a TSSA environment which uses MS SQL Server as the backend DB

Version 4
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    TrueSight Server Automation Suite


    COMPONENT:

    TrueSight Server Automation


    APPLIES TO:

    BSA 8.9.01



    PROBLEM:

    In a TrueSight Server Automation (TSSA) environment which uses MS SQL Server as the backend Database, it is observed that jobs sometimes hang especially when run under higher parallelism.
    * Across multiple runs, the servers which appear as the problem fluctuates.
    * JOB_PART_TIMEOUTs do not work as anticipated.
    * Rerunning the same job against the servers against which it appeared to have hung runs correctly so it is not a target server side issue.

    The DBA confirms that there are deadlocks observed on the SQLServer side e.g.

    ERROR (DBAction:doSelect) - Error occured selecting data: java.sql.SQLException: Transaction (Process ID 53) was deadlocked on 
    lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. 
     


    CAUSE:

    ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT options may not be enabled as required on the SQL Server DB.


    SOLUTION:

    See the following page of the TSSA documentation:

    https://docs.bmc.com/docs/ServerAutomation/89/installing/installing-individual-components/installing-components-on-microsoft-windows/setting-up-a-sql-server-database-and-user-for-bmc-server-automation

    To reduce the likelihood of database deadlocks, execute the following steps:

       
    1. Execute the following query: 
      ALTER DATABASE <db_name> SET ALLOW_SNAPSHOT_ISOLATION ON;
      <db_name> is the name of the database you set up earlier in this procedure.
    2.  
    3. Execute the following additional query:
      ALTER DATABASE <db_name> SET READ_COMMITTED_SNAPSHOT ON;
      <db_name> is the name of the database you set up earlier in this procedure. 
    4.  
    5. Exit SQL Server Management Studio.
      
    The current values can be checked as follows and if either set not enabled, ask the DBA to enable using the above SQL: 
       

    SELECT snapshot_isolation_state_desc from sys.databases where name='bladelogic';   (Should return "On")

      

    SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= 'bladelogic';      (Should return 1)

     


    Article Number:

    000141260


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles