[ SQL Code=8115 SQL State=22003] Arithmetic overflow error converting IDENTITY to data type int.

Products

• CA Service Desk Manager

Issue/Introduction

Unable to access SDM application. 

Excerpt from stdlog: 
================
01/10 11:05:43.09 HBCASDPRDBG bpvirtdb_srvr          11656 ERROR        vdbmisc.c              688 Miscellaneous Database error occured :[Microsoft SQL Server Native Client 11.0] [ SQL Code=3606 SQL State=01000] Arithmetic overflow occurred.; [Microsoft SQL Server Native Client 11.0] [ SQL Code=8115 SQL State=22003] Arithmetic overflow error converting IDENTITY to data type int.
01/10 11:05:43.10 HBCASDPRDBG animator_nxd            4128 ERROR        animator_nxd.c        1839 AHD04199:An unexpected Database error occurred. Contact your administrator. in animator ANI:698053493 (rest_access:2335167) for atev:669065408 in method examine_lock_list
01/10 11:05:43.10 HBCASDPRDBG sqlagt:Procedure0       1568 ERROR        sqlclass.c            1016 SQL Execute  failed: [Microsoft SQL Server Native Client 11.0] [ SQL Code=3606 SQL State=01000] Arithmetic overflow occurred.; [Microsoft SQL Server Native Client 11.0] [ SQL Code=8115 SQL State=22003] Arithmetic overflow error converting IDENTITY to data type int.
01/10 11:05:43.10 HBCASDPRDBG sqlagt:Procedure0       1568 ERROR        sqlclass.c            1017 Clause ({ ? = call dbo.usp_lock_acquire;1( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }) Input (<string>Animator|<string>698053498|<int>18622|<string>domsrvr|<uuid>(NULL)|<string>superuser|<string>ANI|<string>rest_access:2335168|<int>1673328943|<int>630)
01/10 11:05:43.10 HBCASDPRDBG sqlagt:Procedure0       1568 ERROR        sqlsrvr.c             2616 Execution failed. MISC_DB_ERROR: Clause ({ ? = call dbo.usp_lock_acquire;1( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }) Input (<string>Animator|<string>698053498|<int>18622|<string>domsrvr|<uuid>(NULL)|<string>superuser|<string>ANI|<string>rest_access:2335168|<int>1673328943|<int>630)

Environment

CA Service Desk Manager 17.3 

Cause

This error can occur in any SQL Server environment, where a table has repeated inserts and deletes; however the deleted rows are not reclaimed by SQL Server.

Resolution

Arithmetic overflows occur when a data value exceeds the datatype of the column it is going into.  In this case, the overflow occurred on an integer IDENTITY column, which means our value exceeded 2147483647
 This query will return all tables with IDENTITY columns from your database, WITH their current IDENTITY values.  

SELECT

   tables.TABLE_NAME,

   IDENT_SEED(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME) Seed,

   IDENT_INCR(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME) Increment,

   IDENT_CURRENT(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME) Current_Identity

FROM

   INFORMATION_SCHEMA.TABLES tables

WHERE

   OBJECTPROPERTY(OBJECT_ID(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME), 'TableHasIdentity') = 1

   AND tables.TABLE_TYPE = 'BASE TABLE';

Result: 
======

 

In SQL Server Management Studio, login as a user with administrator authority and run the following SQL:

DBCC CHECKIDENT(TableName, RESEED,0)

This query reseeds the primary table identity key back to 1 again, where TableName equals the actual table name that failed on the INSERT.

For the above example, the query would be:

DBCC CHECKIDENT (usp_record_lock, RESEED, 0) 

After running this query, you should now be able to edit tickets.

Recycle Service Desk services.

Additional Information

http://www.sqlfingers.com/2017/11/arithmetic-overflow-error-converting.html

https://stackoverflow.com/questions/2295731/maximum-for-autoincremental-int-primary-key-in-sqlserver