KBI 310216 Arithmetic Overflow Error Converting IDENTITY To Data Type Int

Version

Argent Extended Technology – ALL versions

Date

2 Sep 2010

Summary

The Supervising Engine log for any product may show the following message:

Arithmetic overflow error converting IDENTITY to data type int

Arithmetic overflow occurred

(FILE: ALM_subs.cpp LINE: 1294)

Technical Background

All Argent data tables use an int data type for the idcounter column. In Microsoft SQL Server, this datatype has a maximum value of 2,147,483,647. Over the lifetime of your Argent installation, it is possible that you will write over 2 billion data records to certain tables. As the idcounter is simply incremented in SQL Server, once data exceeds that amount, SQL Server will no longer be able to create new ids, and no new data will be written to the table.

Resolution

To determine which table has the issue, execute the SQL statement:

SELECT IDENT_CURRENT(‘<table_name>’)

against the likely tables. Typical tables include ALC_ARCEVTLOG, AXM_TRACKLOG, and <PROD>_PRD_SLAVE, where <PROD> is the same product abbreviation as the supervising log file prefix.

Once you have identified the table, you need to reset the IDENTITY seed. If the table is empty (due to regular purging of data), then you can safely reseed. Otherwise, you have to ensure that resetting the IDENTITY won’t conflict with existing data. If the minimum ID is high enough, and data is purged regularly, such that the existing data will purge before the data wraps back, then reseeding with existing data is safe. Remember that we’re talking about 2 billion data records, so this will rarely be an issue.

You can check the minimum ID of the table using the SQL statement:

SELECT MIN(<identity_column>) FROM <table_name>

where <identity_column> is the table column set to Identity = True, which in Argent tables is typically ‘idcounter‘ or ‘pckey‘.

Finally to reset the identity seed, execute the following SQL statement:

DBCC CHECKIDENT (‘<table_name>’, reseed, 0)

You can verify that data is now being written using the log files or by executing the SQL to view the minimum ID. New data will be added with an ID beginning at 1 and incrementing from there.