Time Slicing job fails due to deadlock issue on MSSQL

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Time Slicing job fails with the below BG error:

ERROR 2019-01-10 10:00:13,007 [Dispatch Time Slicing : [email protected] (tenant=clarity)] niku.blobcrack (clarity:admin:32880046__64FC16E5-D2BB-4BF0-B73E-E468B87E1AC6:Time Slicing) Error while inserting slices. 
com.niku.union.persistence.PersistenceDeadlockException: 
SQL error code: 1205 
Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 80) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. 
Executed: 
{call PRJ_BLB_SLICE_MERGE_SP(?) } 
Derived from statement: 
<?xml version="1.0" encoding="UTF-8"?> 
<statement batchable="true" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" inputSource="map" xmlns="http://schemas.niku.com/2002/pmd"> 
<sql dbVendor="all"> 
<text> 
{call PRJ_BLB_SLICE_MERGE_SP(?) } 
</text> 
<param name="tableName" type="string" direction="IN" expressionListDelimiter=","/> 
</sql> 
</statement> 

Referenced by: 
<?xml version="1.0" encoding="UTF-8"?> 
<statementRef id="blobcrack.mergeSlices_set_nested_0" inputSource="map" inputMapIndex="0" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" defaultSliceSize="20" ignoreIfError="false" hideResultSets="false" xmlns="http://schemas.niku.com/2002/pmd"/> 

Using input: 
{tableName=PRJ_BLB_SLICES_M_HARDALC}

Cause

Database contention

Environment

Release: All

Component: Time Slicing job

Database: MS SQL

Resolution

1. Contact your Database team to review performance on the server as deadlocks are resolved at the database level.
2. Additionally, reach out to your DBA team to see what configuration can be set to view the capture and view the deadlock graphs. See the following link from Microsoft: Understand and resolve SQL Server blocking problems
3. If there continues to be an issue with the job failing,  contact the Clarity Support team for analysis, as there may be a need to rebuild the slice table, specified in the BG logs.

Additional Information

See also:

• Time Slicing job failures - troubleshooting / known causes

• Time Slicing job fails with deadlock errors - troubleshooting steps