How To Check For Identity Types Reaching Datatype Maximum Before They Cause An Arithmetic Overflow Error

It can be catastrophic for an identity field to reach its maximum value, there is no early warning system in SQL Server, it will just start failing with an error message like:

Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.

Any DBA worth his pay packet will not want this to happen, so here is a little script that we have put together that can be run as part of a health check, and will make sure that any identity columns with integer datatypes have enough overhead at the time of checking.

Then we look at two ways you can address the issue of an identity column reaching the end of its range, should you need to.

-------------------------------------------------
USE DBAFire_DB

DECLARE @maxallowed DECIMAL (3,2)
SET @maxallowed = .75

SELECT
Object_Name(id.id) AS [table_name],
id.name AS [column_name],
t.name AS [data_type],
IDENT_CURRENT(Object_Name(id.id)) AS [last_value],
CASE 
	WHEN t.name = 'tinyint'   THEN 255 
	WHEN t.name = 'smallint'  THEN 32767 
	WHEN t.name = 'int'       THEN 2147483647 
	WHEN t.name = 'bigint'    THEN 9223372036854775807
END AS [max_value]
INTO #tmp
FROM syscolumns id
INNER JOIN systypes t ON id.xtype = t.xtype
INNER JOIN sysobjects so ON id.id = so.id AND so.xtype = 'U'
WHERE id.colstat = 1

SELECT
table_name [Table],
column_name [Column],
data_type [DataType],
last_value [LastValue],
    CASE 
		WHEN last_value < 0 THEN 100
		ELSE (1 - CAST(last_value AS FLOAT(4)) / max_value) * 100 
		END [PercentFree],
	CASE 
		WHEN CAST(last_value AS FLOAT(4)) / max_value >= @maxallowed THEN 'WARNING: approaching or at max limit'
		ELSE 'OK'
        END [Status]
FROM #tmp
ORDER BY DataType DESC,
lastvalue DESC

DROP TABLE #tmp
-------------------------------------------------

Script adapted from this one.

Results of identity check

Running the above script on a given database will give you the below results. Anything over 75% capacity will be reported upon.

identity results

How To Resolve An Identity Column Reaching Max Int Value

There are really two paths you can travel on this issue. One is a quick fix, with minimal impact but not a long term solution. The other is a long term solution but involves increased application impact.

Quick Fix to Identity Column Reaching Maximum INTEGER Value

Here is the quick fix and a sample snippet of code you can use to get yourself out of a crisis.

------------------------- ---
-- RESEED SMALLINT COLUMN ---

DBCC CHECKIDENT ('<<yourtablename>>', RESEED,  -32768)

--- RESEED INT COLUMN -------

DBCC CHECKIDENT ('<<yourtablename>>', RESEED,  -2147483648)

--- RESEED BIGINT COLUMN -------

DBCC CHECKIDENT ('<<yourtablename>>', RESEED,  -9223372036854775808)

------------------------- ---

Longterm Fix to Identity Column Reaching Maximum INTEGER Value

If you go down this path you are going to have to get your hands dirty. This however will set your table on the right track for a more scalable future.

  • Create a copy of the table (with a different name) with the correct integer datatype (e.g change from an INT to a BIGINT)
  • Reseed with a value higher than is currently been used
  • Copy data, if required, from old table to new
  • Switch table names

If there is anything else you need to know around these issues, make sure you drop us a comment!

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

2 Comments

  1. what do you exactly mean by Reseed with a value higher than is currently been used. do you mean if the last value entered is say 2147483647 i should reseed to 2147483648. kindly explain that second part in the steps.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.