Monitoring Identity Column Values

Identity columns are often used for generating key values. Once create it, we often forget about it. However, once the max value allowed by the data type is reached, no more rows can be inserted.

To avoid unexpected outage, we can use sys.identity_columns to monitor if the values are approaching the max limit.  The 6 data types supported are: tinyint, smallint, int, bigint, numeric, and decimal.

For example, we can create 3 tables with identity columns, assign a seed value, and insert some data

create table paul_test1 (a tinyint identity, b char(1))
create table paul_test2 (a int identity, b char(1))
create table paul_test3 (a numeric(6,0) identity, b char(1))

dbcc checkident('paul_test1', reseed, 200)
dbcc checkident('paul_test2', reseed, 2000000000)
dbcc checkident('paul_test3', reseed, 999999)

insert paul_test1 (b) values ('x')
insert paul_test2 (b) values ('x')
insert paul_test3 (b) values ('x')

If we insert one more row on the third table

insert paul_test3 (b) values ('x')

We will get this error:

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

We can query sys.identity_columns to find the identity column last value of a table and calculate the percent used of the positive values. Then we can run the query on each database using sp_MSforeachdb, as shown on the script below (e.g. C:\TEMP\identity_values.sql)

create table #monitor_identity(
instance_name varchar(255)
,database_name varchar(255)
,table_name varchar(255) 
,schema_name varchar(255)
,column_name varchar(255) 
,current_value numeric(38)
,max_value numeric(38)
,pct_used numeric(38,2)
)
insert #monitor_identity
exec sp_MSforeachdb '
select 
@@servername
,''?''
,object_schema_name(object_id, db_id(''?''))
,object_name(object_id, db_id(''?''))
, name
, cast(last_value as numeric(38))
, max_value = case TYPE_NAME(system_type_id)
when ''tinyint'' then 255
when ''smallint'' then 32767
when ''int'' then 2147483647
when ''bigint'' then 9223372036854775807
when ''numeric'' then cast(replicate(''9'',precision) as numeric(38))
when ''decimal'' then cast(replicate(''9'',precision) as numeric(38))
end
, pct_used = cast(last_value as numeric(38)) / case TYPE_NAME(system_type_id)
when ''tinyint'' then 255
when ''smallint'' then 32767
when ''int'' then 2147483647
when ''bigint'' then 9223372036854775807
when ''numeric'' then cast(replicate(''9'',precision) as numeric(38))
when ''decimal'' then cast(replicate(''9'',precision) as numeric(38))
end * 100
from ?.sys.identity_columns with (nolock)
where last_value is not null
'
select
instance_name  
,database_name 
,schema_name 
,table_name 
,column_name 
,current_value 
,max_value 
,pct_used 
from #monitor_identity
where pct_used > 70
order by 
database_name 
,schema_name 
,table_name 
drop table #monitor_identity

Output

To run the script in multiple instances, we can have a list of instances in a text file (e.g. C:\TEMP\instance_list.txt)

instance1
instance2

Then we can easily use a PowerShell script to pipe the instance list content to Invoke-Sqlcmd, and pipe to Out-GridView

Get-Content C:\TEMP\instance_list.txt | Invoke-Sqlcmd -InputFile C:\TEMP\identity_values.sql | Out-GridView