Monitor SQL Server IDENTITY Column Values to Prevent Arithmetic Overflow Errors


By:   |   Updated: 2016-07-29   |   Comments (6)   |   Related: More > Identities

Problem

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?

Solution

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)

INSERT INTO #DataTypeMaxValue VALUES 
   ('tinyint' , 255),
   ('smallint' , 32767),
   ('int' , 2147483647),
   ('bigint' , 9223372036854775807)

-- retrieve identity column information
SELECT 
   distinct OBJECT_NAME (IC.object_id) AS TableName,
   IC.name 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


Last Updated: 2016-07-29


get scripts

next tip button



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.

View all my tips





Comments For This Article




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

Great query Ahmad. Thanks a lot for sharing.


Wednesday, October 03, 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.

-Greg


Wednesday, October 03, 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:

CREATE TABLE MyTable

(

† † ID TINYINT PRIMARY KEY IDENTITY(1, 1),

† † DT DATETIME

);

DECLARE @ct INT = 0;

WHILE @ct <= 255

BEGIN

† † INSERT INTO dbo.MyTable

† † (

† † † † DT

† † )

† † SELECT GETDATE();

† † SET @ct = @ct + 1;

END;


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

SELECT 

   distinct OBJECT_NAME (IC.object_id) AS TableName,

   IC.name AS ColumnName,

   DTM.DataType 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(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 03, 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.



download


Recommended Reading

Populate a SQL Server column with a sequential number not using an identity

Auto generated SQL Server keys with the uniqueidentifier or IDENTITY

SQL Server IDENTITY System Function Comparison

Insert missing SQL Server IDENTITY column values using SET IDENTITY_INSERT command

Using Identity Insert to keep SQL Server table keys in sync





get free sql tips
agree to terms


Learn more about SQL Server tools