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

5 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. 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
  3. I recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often. odzyskiwanie danych Warszawa

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

      Delete