Time Slicing Job Hanging/Processing Indefinitely on SQL Server Database

Products

• Clarity PPM On Premise

Issue/Introduction

Time Slicing job hangs and is stuck in the 'Processing' status.

Environment

Release: All

Resolution

1. Run the following query to check for Time Slicing job progression:

SELECT '(01) Resource Calendar PRCALENDAR' Slice_Object, Count(*), SLICE_STATUS FROM prcalendar WHERE SLICE_STATUS in (1,2,3,4) GROUP BY SLICE_STATUS
UNION
SELECT '(02) Resource Availability PRJ_RESOURCES' Slice_Object, Count(*), SLICE_STATUS FROM
prj_resources WHERE SLICE_STATUS in (1,2,3,4) GROUP BY SLICE_STATUS
union
SELECT '(03) Resource Allocation – Soft PRTEAM' Slice_Object, Count(*), SLICE_STATUS FROM
prteam WHERE SLICE_STATUS in (1,2,3,4) GROUP BY SLICE_STATUS
union
SELECT '(04) Resource Allocation – Hard PRTEAM' SLICE_OBJECT, COUNT(*), HARD_SLICE_STATUS FROM
prteam WHERE HARD_SLICE_STATUS in (1,2,3,4) GROUP BY HARD_SLICE_STATUS
UNION
SELECT '(05) Resource Assignment PRASSIGNMENT' Slice_Object, Count(*), SLICE_STATUS FROM
prassignment WHERE SLICE_STATUS in (1,2,3,4) GROUP BY SLICE_STATUS
UNION
SELECT '(06) Resource Requisition RSM_REQ_REQUISITIONS' Slice_Object, Count(*), SLICE_STATUS FROM
RSM_REQ_REQUISITIONS WHERE SLICE_STATUS in (1,2,3,4) GROUP BY SLICE_STATUS
UNION
SELECT '(07) Capacity Scenario Team CAP_SCENARIO_TEAM' Slice_Object, Count(*), SLICE_STATUS FROM
CAP_SCENARIO_TEAM WHERE SLICE_STATUS in (1,2,3,4) GROUP BY SLICE_STATUS
UNION
SELECT '(08) Capacity Scenario Tasks CAP_SCENARIO_TASKS' Slice_Object, Count(*), SLICE_STATUS FROM
CAP_SCENARIO_TASKS WHERE SLICE_STATUS in (1,2,3,4) GROUP BY SLICE_STATUS
union
SELECT '(09) Capacity Scenario Assignments CAP_SCENARIO_ASSIGNMENTS' Slice_Object, Count(*), SLICE_STATUS FROM
CAP_SCENARIO_ASSIGNMENTS WHERE SLICE_STATUS in (1,2,3,4) GROUP BY SLICE_STATUS
UNION
SELECT '(10) Time Entries PRTIMEENTRY' Slice_Object, Count(*), SLICE_STATUS FROM
PRTIMEENTRY WHERE SLICE_STATUS in (1,2,3,4) GROUP BY SLICE_STATUS

If there is no movement, proceed to the next step.

2. Have SQL Server DBA or resource with access to either:
SQL Server Management Studio > Activity Monitor or
query to check for queries that are blocking.

In one case example, auditing was causing issue with the LCK_M_IS sql wait type.
To resolve, disable the triggers related to the corresponding table.
Cancel, delete the Time Slicing job.
Restart BG service.
Run an immediate Time Slicing job instance.