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:

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.  

   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
   OBJECTPROPERTY(OBJECT_ID(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME), 'TableHasIdentity') = 1

...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:


  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.


    1. Thank you, Muhammad! That is very nice of you to say. Super glad to be providing useful reference. Please don't hesitate to let me know if there's something you're looking for but cannot find. Maybe I can help!


    1. Thank you!! That is really kind of you to say! Please don't hesitate to let me know if you question anything, or want to see more of something. The feedback is great!