Error when trying to edit tickets in SDM: Logs show Arithmetic overflow error converting IDENTITY to data type int

Products

• SUPPORT AUTOMATION- SERVER
• CA Service Desk Manager - Unified Self Service
• CA Service Desk Manager
• CA Service Management - Asset Portfolio Management
• CA Service Management - Service Desk Manager

Issue/Introduction

When trying to edit a Request, Incident, or Problem the following error is thrown on the User Interface:

"AHD05021: Someone else is editing this so you won't be able to make changes. Locked by ". Phone number"

The following errors appear in the stdlog:

sqlagt:Procedure 17344 ERROR sqlclass.c 1010 SQL Execute failed: [Microsoft OLE DB Provider for SQL Server] [ SQL Code=3606 SQL State=01000] Arithmetic overflow occurred.; [Microsoft OLE DB Provider for SQL Server] [ SQL Code=8115 SQL State=22003] Arithmetic overflow error converting IDENTITY to data type int. 

sqlagt:Procedure 17344 ERROR sqlclass.c 1011 Clause ({ ? = call dbo.usp_lock_acquire;1( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }) Input (<string>Call_Req|<string>686887|<int>4360|<string>domsrvr|<uuid>0F39FB9EE9C1464EA7F1A38CB59B4D4A|<string>TCSNUGADM|<string>cr|<string>271782|<int>1535742063|<int>630) 

sqlagt:Procedure 17344 ERROR sqlsrvr.c 2616 Execution failed. MISC_DB_ERROR: Clause ({ ? = call dbo.usp_lock_acquire;1( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }) Input (<string>Call_Req|<string>686887|<int>4360|<string>domsrvr|<uuid>0F39FB9EE9C1464EA7F1A38CB59B4D4A|<string>TCSNUGADM|<string>cr|<string>271782|<int>1535742063|<int>630)

Environment

CA Service Desk 17.1 and higher.

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.

For a full explanation, see the following article:

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

Resolution

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.