MSSQL: Time Slicing Job Is Stuck on Rollover in 16.2.2+

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

On Monthly Rollover, the Time Slicing job has not been completing and is indefinitely stays in the 'Processing' state

The BG logs with INFO logging on the blobcrack component do not show any progression. 

Environment

16.2.2, 16.2.3, 16.3.x with SQL Server Database

Cause

There are blocking transactions caused by audit triggers on table INV_INVESTMENTS on investment sub-objects 

• The audit triggers are meant to keep only Name(For Parent & Grand Parent) to the latest name of the instance.

Resolution

This is DE155705, which will be addressed by a user story as it requires a product redesign (redesign of Investments and custom investments audit triggers). 

Workaround:

1. Connect to the Clarity database (could be when the job is still progressing)
2. Check the existing triggers on INV_INVESTMENTS table
3. Disable all triggers that have a custom sub object name and refer to ODF_AUD_OBJ_INST_CONTEXT
   • example T_TESTOBJECT_IINV_INVESTMENTS and DDL contains references to ODF_AUD_OBJ_INST_CONTEXT
4. Wait until the Time Slicing monthly rollover job completes
5. Enable the triggers back once done

Note: The custom sub object triggers update parent ids in auditing, if parent IDs are not used, you may leave the triggers disabled