Friday, November 3, 2017

Arithmetic overflow error converting IDENTITY to data type int.

One of my customer's SQL Server Agent jobs failed today with this error:

Message
Executed as user: MAJNT\sqlservice. Arithmetic overflow error converting IDENTITY to data type int. [SQLSTATE 22003] (Error 8115)  Arithmetic overflow occurred. [SQLSTATE 01000] (Error 3606).  The step failed.

We know these 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 --  wow!  But how do we know which table the overflow occurred in?

Easy peasy.  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';

...and your results:



No more data is going into this table until the problem is resolved, so you may consider running something like this on a scheduled basis.  Then you can be aware of the values before they exceed their maximums allowed.

What are your current options?  
  1. Archive the data, reseed the IDENTITY and begin taking in new records.
  2. Alter the IDENTITY column from INT to BIGINT, increasing our maximum range value to 9,223,372,036,854,775,807.

See this for more details regarding the IDENTITY value, checking it and reseeding it:
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql

1 comment:

  1. check to see if the values less than zero have been used. You can change to use them until you have time for a more permanent fix.

    ReplyDelete