'Load Data Warehouse' job stuck in a SQL Server based implementation

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

• It is noticed that the 'Load Data Warehouse' job is stuck
• The DB used by the application is MS SQL Server
• No errors are logged in bg-dwh.log or bg-ca.log.

Cause

Deadlocks in the DB.

Resolution

A sample query is being provided below. It can be used to get a list of all sessions in the DB. By looking at the queries that are stuck, a decision can be made as to whether the job is stuck due to a deadlock.

SELECT 
    r.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    DB_NAME(r.database_id) AS database_name,
    r.start_time,
    r.status,
    r.command,
    t.text AS query_text,
    r.wait_type,
    r.wait_time,
    r.cpu_time,
    r.total_elapsed_time,
    r.reads,
    r.writes,
    r.logical_reads
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s 
    ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id <> @@SPID -- Excludes this exact query from the results
  AND s.is_user_process = 1; -- Excludes background system processes

Note: It is recommended not to kill sessions indiscriminately. Contact Broadcom Support for assistance if required.