How do I easily find IDENTITY columns in danger of overflowing?

Question

My database is getting old, and one of my biggest INT IDENTITY columns has a value around 1.3 billion. This will overflow around 2.1 billion. I plan on increasing it's size, but I don't want to do it too soon because of the number of records in the database. I may replace my database hardware before I increase the column size, which could offset any performance problems this could cause. I also want to keep an eye on all the other columns in my databases that are more than 50% full. It's a lot of tables, and checking each one manually is not practical.

This is how I am getting the value now (I know the value returned may be slightly out of date, but it's good enough for my purposes):

PRINT IDENT_CURRENT('MyDatabase.dbo.MyTable')

Can I use the INFORMATION_SCHEMA to get this information?

Handout

This is not an answer, but have you started your identities from 0 or 1? If yes, then instead of increasing column size, have you considered resetting identity to the smallest int? That would give you additional 2 billion out of the same column size. – Voiced

@AlexKuznetsov: Yes, I have considered it. I may use negative values in future projects, but there is so much code written for this database I don't want to risk breaking it. – Handout

Here and here is why. According to the SO FAQ, this behavior is not only acceptable, but it is encouraged. I will continue to do it until it is considered bad etiquette by the SO community. – Handout

Answer 1

You can consult the sys.identity_columns system catalog view:

SELECT     
    name,
    seed_value, increment_value, last_value
FROM sys.identity_columns

This gives you the name, seed, increment and last value for each column. The view also contains the data type, so you can easily figure out which identity columns might be running out of numbers soonish...

Inwardness

Note that for cross database views, last_value may be incorrectly reported as null. IDENT_CURRENT(object_name(object_id)) will return the correct last_value. – Decalescence

Answer 2

I created a stored procedure to solve this problem. It uses the INFORMATION_SCHEMA to find the IDENTITY columns, and then uses IDENT_CURRENT and the column's DATA_TYPE to calculate the percent full. Specify the database as the first parameter, and then optionally the minimum percent and data type.

EXEC master.dbo.CheckIdentityColumns 'MyDatabase' --all

EXEC master.dbo.CheckIdentityColumns 'MyDatabase', 50 --columns 50% full or greater

EXEC master.dbo.CheckIdentityColumns 'MyDatabase', 50, 'int' --only int columns

Example output:

Table                     Column             Type    Percent Full Remaining
------------------------- ------------------ ------- ------------ ---------------
MyDatabase.dbo.Table1     Table1ID           int     9            1,937,868,393
MyDatabase.dbo.Table2     Table2ID           int     5            2,019,944,894
MyDatabase.dbo.Table3     Table3ID           int     9            1,943,793,775

I created a reminder to check all my databases once per month, and I log this information in a spreadsheet.

CheckIdentityColumns Procedure

USE master
GO

CREATE PROCEDURE dbo.CheckIdentityColumns
    (
    @Database       AS NVARCHAR(128),
    @PercentFull    AS TINYINT          = 0,
    @Type           AS VARCHAR(8)       = NULL
    )

AS

--this procedure assumes you are not using negative numbers in your identity columns

DECLARE @Sql NVARCHAR(3000)

SET @Sql = 
'USE ' + @Database + '

SELECT                        
    [Column].TABLE_CATALOG + ''.'' +
    [Column].TABLE_SCHEMA + ''.'' +
    [Table].TABLE_NAME          AS [Table],
    [Column].COLUMN_NAME                        AS [Column],
    [Column].DATA_TYPE              AS [Type],
    CAST((
    CASE LOWER([Column].DATA_TYPE)
    WHEN ''tinyint''
    THEN (IDENT_CURRENT([Table].TABLE_NAME) / 255)  
    WHEN ''smallint''
    THEN (IDENT_CURRENT([Table].TABLE_NAME) / 32767)  
    WHEN ''int''
    THEN (IDENT_CURRENT([Table].TABLE_NAME) / 2147483647)  
    WHEN ''bigint''
    THEN (IDENT_CURRENT([Table].TABLE_NAME) / 9223372036854775807)  
    WHEN ''decimal''
    THEN (IDENT_CURRENT([Table].TABLE_NAME) / (([Column].NUMERIC_PRECISION * 10) - 1))  
    END * 100) AS INT)                  AS [Percent Full],
    REPLACE(CONVERT(VARCHAR(19), CAST(
    CASE LOWER([Column].DATA_TYPE)
    WHEN ''tinyint''
    THEN (255 - IDENT_CURRENT([Table].TABLE_NAME))  
    WHEN ''smallint''
    THEN (32767 - IDENT_CURRENT([Table].TABLE_NAME))  
    WHEN ''int''
    THEN (2147483647 - IDENT_CURRENT([Table].TABLE_NAME))  
    WHEN ''bigint''
    THEN (9223372036854775807 - IDENT_CURRENT([Table].TABLE_NAME))  
    WHEN ''decimal''
    THEN ((([Column].NUMERIC_PRECISION * 10) - 1) - IDENT_CURRENT([Table].TABLE_NAME))  
    END
    AS MONEY) , 1), ''.00'', '''')              AS Remaining


FROM                       
    INFORMATION_SCHEMA.COLUMNS                  AS [Column]

    INNER JOIN    
    INFORMATION_SCHEMA.TABLES                   AS [Table]
    ON      [Table].TABLE_NAME                  = [Column].TABLE_NAME

WHERE
    COLUMNPROPERTY(
        OBJECT_ID([Column].TABLE_NAME), 
        [Column].COLUMN_NAME, ''IsIdentity'') = 1 --true
    AND [Table].TABLE_TYPE                      = ''Base Table'' 
    AND [Table].TABLE_NAME                      NOT LIKE ''dt%'' 
    AND [Table].TABLE_NAME                      NOT LIKE ''MS%'' 
    AND [Table].TABLE_NAME                      NOT LIKE ''syncobj_%''
    AND CAST(
    (
    CASE LOWER([Column].DATA_TYPE)
    WHEN ''tinyint''
    THEN (IDENT_CURRENT([Table].TABLE_NAME) / 255)  
    WHEN ''smallint''
    THEN (IDENT_CURRENT([Table].TABLE_NAME) / 32767)  
    WHEN ''int''
    THEN (IDENT_CURRENT([Table].TABLE_NAME) / 2147483647)  
    WHEN ''bigint''
    THEN (IDENT_CURRENT([Table].TABLE_NAME) / 9223372036854775807)  
    WHEN ''decimal''
    THEN (IDENT_CURRENT([Table].TABLE_NAME) / (([Column].NUMERIC_PRECISION * 10) - 1))  
    END * 100
    ) AS INT)                                    >= ' + CAST(@PercentFull AS VARCHAR(4))

IF (@Type IS NOT NULL) 
    SET @Sql = @Sql + 'AND  LOWER([Column].DATA_TYPE)    = ''' + LOWER(@Type) + ''''

SET @Sql = @Sql + '

ORDER BY
    [Column].TABLE_CATALOG + ''.'' +
    [Column].TABLE_SCHEMA + ''.'' +
    [Table].TABLE_NAME,
    [Column].COLUMN_NAME'

EXECUTE sp_executesql @Sql
GO

Handout

Answer 3

Keith Walton has a very comprehensive query that is very good. Here's a little simpler one that is based on the assumption that the identity columns are all integers:

SELECT sys.tables.name AS [Table Name], 
    last_value AS [Last Value],     
    MAX_LENGTH,
    CAST(cast(last_value as int) / 2147483647.0 * 100.0 AS DECIMAL(5,2)) 
        AS [Percentage of ID's Used], 
    2147483647 - cast(last_value as int) AS Remaining
FROM sys.identity_columns
    INNER JOIN sys.tables
        ON sys.identity_columns.object_id = sys.tables.object_id
ORDER BY last_value DESC

The results will look like this:

Table Name      Last Value      MAX_LENGTH  Percentage of ID's Used   Remaining
My_Table        49181800             4               2.29             2098301847

Checking Integer Identity Columns

Cat

Answer 4

While crafting a solution for this problem, we found this thread both informative and interesting (we also wrote a detailed post about this and described how our tool works).

In our solution we're querying the information_schema to acquire a list of all columns. Then we wrote a program that would go through each of them and compute the maximum and minimum (we account for both overflow and underflow).

SELECT
    b.COLUMN_NAME,
    b.COLUMN_TYPE,
    b.DATA_TYPE,
    b.signed,
    a.TABLE_NAME,
    a.TABLE_SCHEMA
FROM (
    -- get all tables
    SELECT
    TABLE_NAME, TABLE_SCHEMA
    FROM information_schema.tables
    WHERE 
    TABLE_TYPE IN ('BASE TABLE', 'VIEW') AND
    TABLE_SCHEMA NOT IN ('mysql', 'performance_schema')
) a
JOIN (
    -- get information about columns types
    SELECT
    TABLE_NAME,
    COLUMN_NAME,
    COLUMN_TYPE,
    TABLE_SCHEMA,
    DATA_TYPE,
    (!(LOWER(COLUMN_TYPE) REGEXP '.*unsigned.*')) AS signed
    FROM information_schema.columns
) b ON a.TABLE_NAME = b.TABLE_NAME AND a.TABLE_SCHEMA = b.TABLE_SCHEMA
ORDER BY a.TABLE_SCHEMA DESC;

Garik