Monitor SQL Server IDENTITY Column Values to Prevent Arithmetic Overflow Errors

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

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *