Monitor SQL Server IDENTITY Column Values to Prevent Arithmetic Overflow Errors

By:   |   Comments (6)   |   Related: > Identities


As a SQL Server DBA, one of the most important tasks is monitoring database growth to make sure systems connecting to the database are always able to read and write to the database. I experienced a strange case recently; users were not able to write to one of our tables although the database had enough free space. After checking the SQL Server Error Log, I found the table had an IDENTITY column that reached its ceiling value.  Is there a way to be proactive and detect this issue without interrupting the database users?


In SQL Server an IDENTITY column is defined on a table to provide an automatic increment for the columnís value per each INSERT operation.  This is generally used to give each row a distinct value. Only one IDENTITY column can be defined per table along with two parameters; the seed value and the increment amount (by default these values are both 1).  At times, the IDENTITY column can be a good candidate to be defined as a primary key, as it has increasing values without taking up much storage space.

In order to take advantage of the IDENTITY columnís benefits without experiencing issues, you should be proactive and keep an eye on the IDENTITY column growth. If the IDENTITY column reaches its maximum limit and you try to insert a new value, the below error will be raised:

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

The error indicates an overflow occurred in the INDENTITY column. This error is due to when you try to insert a new value into the table and the IDENTITY column reached its maximum value. To overcome this issue, you have the choice to delete all of the tableís data using the TRUNCATE statement which will reset the identity seed value and as you know this is not applicable in production environments. Another choice is to change the IDENTITY columnís datatype and use a larger datatype if possible, such as changing smallint to int or int to bigint.

To avoid getting into an overflow issue, the below script can be used to monitor the IDENTITY usage. The first step creates a temp table in which we input the maximum values for each datatype that are commonly used to define an IDENTITY column. The sys.identity_columns system table is used to retrieve the IDENTITY column information for each table that has an IDENTITY column and pulls back the table name, the column name, the datatype, the seed value, increment value and last value used. The sys.identity_columns table is joined with the temp table created to get the maximum limit for that IDENTITY column. Then it is joined with the sys.tables, sys.dm_db_partition_stats and sys.indexes system tables and views to get the tableís number of rows.

The final version of the script that returns a single record for each table that has IDENTITY column shows the IDENTITY usage percent and how far we are from reaching the IDENTITY maximum value.

-- define the max value for each data type
CREATE TABLE #DataTypeMaxValue (DataType varchar(50), MaxValue bigint)

   ('tinyint' , 255),
   ('smallint' , 32767),
   ('int' , 2147483647),
   ('bigint' , 9223372036854775807)

-- retrieve identity column information
   distinct OBJECT_NAME (IC.object_id) AS TableName, AS ColumnName,
   TYPE_NAME(IC.system_type_id) AS ColumnDataType,
   DTM.MaxValue AS MaxDataTypeValue,
   IC.seed_value IdentitySeed,
   IC.increment_value AS IdentityIncrement, 
IC.last_value, DBPS.row_count AS NumberOfRows, (convert(decimal(18,2),CONVERT(bigint,IC.last_value)*100/DTM.MaxValue)) AS ReachMaxValuePercent FROM sys.identity_columns IC JOIN sys.tables TN ON IC.object_id = TN.object_id JOIN #DataTypeMaxValue DTM ON TYPE_NAME(IC.system_type_id)=DTM.DataType JOIN sys.dm_db_partition_stats DBPS ON DBPS.object_id =IC.object_id JOIN sys.indexes as IDX ON DBPS.index_id =IDX.index_id WHERE DBPS.row_count >0 ORDER BY ReachMaxValuePercent desc DROP TABLE #DataTypeMaxValue

The scriptís result will be as below:

SQL Server Identity column values and remaining number of records before an issue occurs

From the results, you should be proactive and make the decision of how you can prevent a table from reaching its maximum IDENTITY value. You can consider any value for ReachMaxValuePercent over 80% as critical and requiring action on your part.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelorís degree in computer engineering as well as .NET development experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, July 8, 2020 - 4:31:51 AM - Prashant Back To Top (86100)

Great query Ahmad. Thanks a lot for sharing.

Wednesday, October 3, 2018 - 1:59:28 PM - Greg Robidoux Back To Top (77830)

Hi Roman,

I just tweaked the code a bit.  It looks like it is working.  I didn't try with a BIGINT.  Just used the code you supplied to test.


Wednesday, October 3, 2018 - 1:38:52 PM - Roman Rehak Back To Top (77829)

The query throws an error when the identity value is high and the value in†ReachMaxValuePercent gets close to 100%. The error -†Arithmetic overflow error converting bigint to data type numeric.

Code to reproduce:






DECLARE @ct INT = 0;

WHILE @ct <= 255


† † INSERT INTO dbo.MyTable

† † (

† † † † DT

† † )


† † SET @ct = @ct + 1;


Thursday, September 28, 2017 - 9:48:52 AM - John Zabroski Back To Top (66683)

 This has a small bug in it - you will always display 0.0000000 for values below 1%, becaus of when you choose to round.

Change the case from bigint to float and it will display correctly:


   (convert(decimal(9,7),CONVERT(float,IC.last_value)*100/DTM.MaxValue)) AS ReachMaxValuePercent 


Thursday, August 11, 2016 - 11:41:59 AM - Robert Back To Top (43103)

 This query has the potential for collation errors like "Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_100_CI_AS_KS_WS_SC" in the equal to operation."

The query below removes this vulnerability.



CREATE TABLE #DataTypeMaxValue (DataType varchar(10), DataTypeID int, MaxValue bigint)


INSERT INTO #DataTypeMaxValue (DataType, DataTypeID, MaxValue)

VALUES ('tinyint', 48, 255)

, ('smallint', 52, 32767)

, ('int', 56, 2147483647)

, ('bigint', 127, 9223372036854775807)


-- retrieve identity column information


   distinct OBJECT_NAME (IC.object_id) AS TableName, AS ColumnName,

   DTM.DataType AS ColumnDataType,

   DTM.MaxValue AS MaxDataTypeValue,

   IC.seed_value IdentitySeed,

   IC.increment_value AS IdentityIncrement, 


   DBPS.row_count AS NumberOfRows,

   (convert(decimal(9,7),CONVERT(bigint,IC.last_value)*100.0/DTM.MaxValue)) AS ReachMaxValuePercent 

FROM sys.identity_columns IC

   JOIN sys.tables TN ON IC.object_id = TN.object_id

   JOIN #DataTypeMaxValue DTM ON IC.system_type_id=DTM.DataTypeID

   JOIN sys.dm_db_partition_stats DBPS ON DBPS.object_id =IC.object_id 

   JOIN sys.indexes as IDX ON DBPS.index_id =IDX.index_id 

WHERE DBPS.row_count >0 

ORDER BY ReachMaxValuePercent desc


DROP TABLE #DataTypeMaxValue


Wednesday, August 3, 2016 - 4:03:26 AM - Alex Friedman Back To Top (43038)

This is indeed important, thanks for posting. 

A small note: the percent in your query will always be int, so maybe either multiply by 100.0 instead of 100, or show the percents as ints without the decimals.

get free sql tips
agree to terms