I use identity columns frequently. After all, identity columns make great clustering keys. But it’s important when using identity columns to check on the amount of values you have left before you reach the limit of your data type. An identity column has a fixed amount of values it can use based upon whether you specified tinyint, smallint, int, or bigint when you defined the column. If you reach this limit, your inserts will blow up and cause a Chernobyl-like SQL meltdown will begin to fail. I just finished an audit of my tables and thought I’d share the script. I would like to warn that this script is *not* perfect… namely, it doesn’t handle negative integer values very elegantly. It also doesn’t know if you started your seed at zero, approached your max positive limit, then reseeded to the negative limit (see my “quick and dirty fix” tip at the end of this article).
/* Define how close we are to the value limit before we start throwing up the red flag. The higher the value, the closer to the limit. */ Declare @threshold decimal(3,2) = .85; /* Create a temp table */ Create Table #identityStatus ( database_name varchar(128) , table_name varchar(128) , column_name varchar(128) , data_type varchar(128) , last_value bigint , max_value bigint ); /* Use an undocumented command to run a SQL statement in each database on a server */ Execute sp_msforeachdb ' Use [?]; Insert Into #identityStatus Select ''?'' As [database_name] , Object_Name(id.object_id, DB_ID(''?'')) As [table_name] , id.name As [column_name] , t.name As [data_type] , Cast(id.last_value As bigint) 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] From sys.identity_columns As id Join sys.types As t On id.system_type_id = t.system_type_id Where id.last_value Is Not Null'; /* Retrieve our results and format it all prettily */ Select database_name , table_name , column_name , data_type , last_value , Case When last_value < 0 Then 100 Else (1 - Cast(last_value As float(4)) / max_value) * 100 End As [percentLeft] , Case When Cast(last_value As float(4)) / max_value >= @threshold Then 'warning: approaching max limit' Else 'okay' End As [id_status] From #identityStatus Order By percentLeft; /* Clean up after ourselves */ Drop Table #identityStatus; |
If you find yourself quickly approaching your max limit and need to implement a quick and dirty fix, you can reseed your identity column. Of course, this only works if you started at zero instead of the actual lower, negative limit.
the beginning is not working on db names with space or dash
change to
Use [?];
pls
The initial join of sys.identity_columns and sys.types fails by delivering too many rows when the identity column is defined with a UDT. Below is a possibly more robust replacement for that part of the query.
select T.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE, IDENT_CURRENT(T.TABLE_NAME) as last_value
, Case
When C.DATA_TYPE = ‘tinyint’ Then 255
When C.DATA_TYPE = ‘smallint’ Then 32767
When C.DATA_TYPE = ‘int’ Then 2147483647
When C.DATA_TYPE = ‘bigint’ Then 9223372036854775807
When C.DATA_TYPE in (‘numeric’, ‘decimal’)
then cast(power(cast(10 as float),(C.NUMERIC_PRECISION – C.NUMERIC_SCALE)) AS decimal(26)) -1
End As [max_value]
from INFORMATION_SCHEMA.COLUMNS C
JOIN INFORMATION_SCHEMA.TABLES T
on T.TABLE_TYPE = ‘BASE TABLE’
and T.TABLE_NAME = C.TABLE_NAME
AND COLUMNPROPERTY(object_id(T.TABLE_NAME), C.COLUMN_NAME, N’IsIdentity’) = 1
I had to change the first line to:
DECLARE @threshold DECIMAL(3,2)
SET @threshold = .85
As SQL Server 2005 complained ‘Cannot assign a default value to a local variable.’
Otherwise, it worked great!
Thanks
Hi Michelle
Great post, thanks for sharing.
Is there limit to the comment size? I’m trying to post a solution that
I’ve updated to cater for numeric and decimal identity columns as well as (hopefully) showing free space for identity columns that were seeded from the largest negative value at table creation as well as identity fields that have been re-seeded to the largest negative value after the table was created (as per the workaround above).
My code assumes that if an identity column has been re-seeded, there has only been one re-seed and that the re-seed value was to the maximum negative value.
Thanks
Andy
Thanks for the script! And thanks Bodhi for the revision!
Brilliant! You are doing great Job
Hope will learn alot from your articles! Keep it up!
Pingback: Identifying Identity Columns Approaching Their Limit - SQL Server - SQL Server - Toad World