question

sp_lock avatar image
sp_lock asked

Monitoring identity columns

Hey all Just want to run something past you all... The other day we had an issue with an application that basically maxed out the 2.1 billion+ limit in just a few hours on a identity (int) column. I was lucky enough that this table was a staging table can could be truncated. Problem solve and application fixed. My question is: **Do you monitor on a regular basis the current identity field for any given table. I so how do you achieve this. As when I ran a DBCC CHECKIDENT on this particual column it took over 5 minutes.** Thanks JL
sql-server-2005identityint
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Matt Whitfield avatar image
Matt Whitfield answered
IDENT_CURRENT is your friend :) Edit -> Wow, that was really embarrasing. I wrote a proc a few years back to gather those stats, and it was huge. Now it's eminently simple. I've just written it here as a multi-statement function, but it could just as easily be an in-line function. I really hope no-one saw this post before I edited it!! CREATE FUNCTION [dbo].[fn_GetIdentityColumnValues]() RETURNS @Results TABLE (TableName sysname, IdentityColumnName sysname, CurrentIdentityValue [numeric](38,0)) AS BEGIN INSERT INTO @Results (TableName, IdentityColumnName, CurrentIdentityValue) SELECT QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + '.' + QUOTENAME(OBJECT_NAME([object_id])), QUOTENAME([name]), IDENT_CURRENT(QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + '.' + QUOTENAME(OBJECT_NAME([object_id]))) FROM [sys].[identity_columns] RETURN END Ok, here's another one (just a plain SELECT), that gives you the maximum value for integer and numeric / decimal columns. Note that it doesn't go all the way up to the limit on numeric and decimal columns, because POWER() epic fails when raising 10 to the power of 29 or more, and gets the wrong value on 10 ^ 28. SELECT [table_name], [identity_column_name], [next_value], [max_value], [max_value] - [next_value] AS [values_left] FROM ( SELECT QUOTENAME(OBJECT_SCHEMA_NAME([ic].[object_id])) + '.' + QUOTENAME(OBJECT_NAME([ic].[object_id])) AS table_name, QUOTENAME([ic].[name]) AS identity_column_name, ISNULL(CONVERT(NUMERIC (38, 0), [ic].[last_value]) + CONVERT(NUMERIC (38, 0), [ic].[increment_value]), CONVERT(NUMERIC (38, 0), [ic].[seed_value]) + CONVERT(NUMERIC (38, 0), [ic].[increment_value])) AS next_value, CASE WHEN system_type_id IN (48, 52, 56, 127) THEN POWER(CONVERT(NUMERIC(38, 0), 2), (max_length * 8) - 1) - 1 WHEN system_type_id IN (106, 108) THEN POWER(CONVERT(NUMERIC(38, 0), 10), CASE WHEN [precision] - [scale] < 29 THEN ([precision] - [scale]) - 1 ELSE 27 END) - 1 ELSE 0 END AS max_value FROM [sys].[identity_columns] AS [ic] INNER JOIN [sys].[objects] AS [o] ON [o].[object_id] = [ic].[object_id] WHERE [o].[type] = 'U' ) tableData
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Matt... I can post you first question if you like? In your option you do this?
0 Likes 0 ·
I do something similar, but manually. One extension I would make is to modify it so that it works out what the full range of the column is based on it's data type, and shows a percentage of how far the table is through it's identity range, and then introduce a parameter to the function to show identity rows above that threshold, or something...
0 Likes 0 ·
@Matt Whitfield This is a very useful script, I am so glad that I ran into reading this post! I also have a question which I wanted to ask for a long time: what is the difference between **ident_current('the_table')** and the value in the **last_value** column of the **sys.identity_columns** view corresponding to the same table?
0 Likes 0 ·
@Oleg - IDENT_CURRENT will return the IDENT_SEED if there is no last value... to be honest, using last_value would probably be a better plan...
0 Likes 0 ·
@Matt Whitfield Thank you, it is good to know about ident_seed in place of lacking last value. Before, I played with inserts/rollbacks trying to find a situation when they are out of sync (so I could figure out what is the difference), but it never happened.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.