Arithmetic overflow error on IDENTITY column Tasks.task_id

Overview

It seems the Arithmetic overflow error started the day after the v8.0.29 upgrade. The same error happened on an older version (v8.0.12) and the IDENTITY was reset.

This is observed on SQL server 2017 with identity cache enabled by default on the whole DB instance. Other tables like SERHIST, TRANHIST, etc., use T_AUTO_IDENTITY and are not experiencing this issue.

Solution

The issue is related to the new identity feature from SQL Server 2012 which is designed to boost the performance of high-end machines.

Starting with SQL Server 2012, Microsoft has introduced preallocation for identity value. The identity properties are stored separately in the metadata. If a value is used in identity and increment is called, then the new seed value will be set. The operations such as Rollback, Failover, or even unexpected restart can change the seed value except DBCC reseed. 

Additional information may be found in the following external articles:

• SQL SERVER - Identity Jumping 1000 - IDENTITY_CACHE - SQL Authority with Pinal Dave
• Learn to avoid an identity jump issue (IDENTITY_CACHE) with the help of Trace Command (-T272) (sqlshack.com).
  

The Arithmetic Overflow error received means the value of IDENTITY is exceeding the range of data type of that particular column. By default, the Task_Id column has T_Auto_Identity custom data type and its base type is Int and precision 10:

To resolve the Arithmetic Overflow error, any of the following solutions can be executed:

• Remove or shift old data from the Task table.
• Reseed the identity after updating the Task_id manually.
  Note: You will also need to update the foreign key IDs in the Task_Links table (if any).
• Set the database-level setting of IDENTITY_CACHE=OFF:
  

  USE DatabaseName
  GO
  ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
  GO
  

• Add -T272 in SQL Server startup parameters to disable the identity cache:

  • Open the property for SQL Server Services in SQL Server Configuration Manager > Properties > Startup Parameters:
    

  • Adding a TRACE flag 272 in SQL Server instance property using -T272 in the Startup Parameters tab:
    

  • Restart the SQL Server Instance service to apply the changes.

• Change the datatype for the column to BIGINT.

Testing

The steps above should prevent the Arithmetic overflow error to manifest again. In case the error persists, please contact support to troubleshoot the issue further.