Identifying Identity Columns Approaching Their Limit

Back in December, a couple of checks I wrote were included in Brent Ozar Unlimited’s sp_Blitz script. I was chagrined to see that some people discovered some bugs in my code and submitted fixes. To be fair, one was a bug in SQL 2008 & 2008 R2 where DBCC DBINFO WITH TABLERESULTS returned the dbccLastKnownGood entry twice. (Microsoft has said they aren’t going to fix this, unfortunately.) The other was an issue when systems were using the British date format. I guess this just goes to show how hard it is to test every possible use case to discover bugs.

Even though there were only problems with one of the three tests I submitted, I still felt the need to redeem myself and submit another check. I had recently read a blog post about monitoring the values of identity columns in tables to make sure you aren’t approaching the maximum value for the data type used. Surprisingly, such a check wasn’t already in sp_Blitz. I felt this was something I could tackle.

My first attempt worked, but it did so by cycling through all the tables in a database, finding any identity columns, and comparing the maximum value used to the maximum value the data type can accommodate (255 for TINYINT, 2147483648 for INT, etc.) I submitted this and the feedback from Brent was that he wasn’t sure if this should be included because of the performance implications. Although I tested the code on all my servers without issue, Brent mentioned that some databases, especially those for SAP, may have 10,000 to 50,000 tables. Running code to look at each of those tables could have a severe impact on performance or, at the very least, greatly increase the time it took the script to complete. That’s a valid point (and again goes to show how hard it is to test for all use cases). Apparently, he spoke to Kendra Little about this and she brought up a system table that both Brent and I didn’t know about (or more likely, knew about, but forgot): sys.identity_columns. Using that, I can get the same information by querying one system table instead of each individual user table in the database.

So I re-wrote my script to use that and re-submitted it to sp_Blitz. It’s in the queue for evaluation, so I don’t know when (or if) it will be included, but I thought I post the test here. (And of course, once I had the name of this table, I discovered a Google search returns lots of scripts that provide the same functionality.)

A quick note: The script assumes the identity increment is positive and the identity values are positive. The script will issue a warning when the maximum value is more than 90% of the maximum value of the data type used.

  DECLARE @SchemaName sysname
  DECLARE @TableName sysname
  DECLARE @ColumnName sysname

  DECLARE @Name sysname
  DECLARE DBNameCursor CURSOR
  FOR
          SELECT    name
          FROM      sys.databases
          WHERE     source_database_id IS NULL	/* no database snapshots */
                    AND is_read_only = 0		/* no read-only dbs (log shipping) */
                    AND database_id <> 2		/* skip tempdb */
                    AND state = 0	/* online databases */
          ORDER BY  name;

  CREATE TABLE #IdentityCheck
         (
          PK INT IDENTITY(1, 1)
         ,DatabaseName sysname
         ,SchemaName sysname
         ,TableName sysname
         ,ColumnName sysname
         ,ColumnType VARCHAR(8)
         ,MaxValue BIGINT
         )

  OPEN DBNameCursor
  FETCH NEXT FROM DBNameCursor INTO @Name
  WHILE @@fetch_status = 0
        BEGIN

              EXEC('USE [' + @Name + '];  INSERT  INTO #IdentityCheck
              SELECT ''' + @Name + ''' as DatabaseName
              ,SCHEMA_NAME(o.SCHEMA_ID) AS SchemaName
              ,OBJECT_NAME(o.OBJECT_ID) AS TableName
              ,c.[name] AS ColumnName
              ,t.name AS ColumnType
              ,CAST(last_value AS bigint) AS MaxValue
              FROM    sys.identity_columns c
              ,sys.types t
              ,sys.objects o
              WHERE   c.is_identity = 1
              AND t.system_type_id = c.system_type_id
              AND o.object_id = c.object_id
              AND o.type = ''u''
              AND t.is_user_defined = 0 /* only look at system defined types */
              AND t.name IN (''int'', ''bigint'', ''smallint'', ''tinyint'')')
              FETCH NEXT FROM DBNameCursor INTO @Name
        END

  CLOSE DBNameCursor
  DEALLOCATE DBNameCursor

  SELECT    'Table [' + SchemaName + '].[' + TableName + '], column ['
            + ColumnName + '] in database [' + DatabaseName
            + '] is an identity column that has reached over 90% of the maximum value for that datatype.' AS Details
           ,'' AS ChangeRequirements
  FROM      #IdentityCheck
  WHERE     1 = CASE WHEN ColumnType = 'bigint'
                          AND (CONVERT(DECIMAL(19, 0), MaxValue)
                               / 9223372036854775807.0) > .90 THEN 1
                     WHEN ColumnType = 'int'
                          AND (CONVERT(DECIMAL(10, 0), MaxValue)
                               / 2147483648.0) > .90 THEN 1
                     WHEN ColumnType = 'smallint'
                          AND (CONVERT(DECIMAL(5, 0), MaxValue) / 32767.0) > .90
                     THEN 1
                     WHEN ColumnType = 'tinyint'
                          AND (CONVERT(DECIMAL(3, 0), MaxValue) / 255.0) > .90
                     THEN 1
                END

  DROP TABLE #IdentityCheck

So what do you do if you get this warning? Well, a quick fix that Michelle Ufford suggested, is to reseed your identity value to the lowest negative value the data type supports. For example, if your identity column is an integer, reseed the column to start at -2147483648 and increment by a positive number. (This assumes you started your initial identity value at zero and used a positive increment to begin with.)

One more note: you’ll notice my script uses a cursor to cycle through the databases while many other scripts you find on the internet use ms_foreachdb. Either method works. I prefer the cursor because it’s 100% supported. (For the cursors-give-sucky-performance crowd, the undocumented command uses a cursor behind the scenes anyway, so that’s a moot point.) Ms_foreachdb is an unsupported command and can change at any time. There is also evidence that it can skip some databases and even completely lock up SQL 2005 servers in some situations. Given the issue I ran into above with the DBCC DBINFO command, which is also an unsupported command, and Microsoft’s statement that they are not going to fix it, I am committed to not using any undocumented commands in any scripts I pan to re-use. I’ll use them for any one-off work I might be doing, but for something that I want to save and re-use as part of my SQL toolkit, I will pass.

2 thoughts on “Identifying Identity Columns Approaching Their Limit

  1. so what happens if your id column does not start at zero but say a random number like 1122544. how would the reseed be done?

  2. I’d say you can still reseed with the lowest negative number the datatype supports and increment by a positive number. Be sure to test that your client application can handle this (if it uses the identity value at all).

Leave a Reply

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

I am a real person and can prove it by doing this math problem: (required) Time limit is exhausted. Please reload CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.