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

4 comments:

  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
  2. There is not a command to transfer ownership of the station, but you can easily destroy it with the console. Select the FO, open the console Stellaris console Commands

    ReplyDelete
  3. Nice post! This is a very nice blog that I will definitively come back to more times this year! Thanks for informative post. odzyskiwanie danych

    ReplyDelete
    Replies
    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!

      Delete