My Identity Column Runneth Over

Have any of you seen a table with an identity column fill?  It’s not pretty….

Me, the first (only) time an identity column filled on a table.

Once was enough for me!  Here’s a script to save you from your identity column wiping the floor with both you and your patience.  It will warn you when you when a column is within 90% of filling:

/*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*
                                     START HERE

_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_**/


IF OBJECT_ID('tempdb..#t') IS NOT NULL
    DROP TABLE #t;
GO

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'Sandbox')
    CREATE DATABASE Sandbox;
GO

--Let's create a database to play with on a NON-production server....

USE [Sandbox];
GO

--We'll create a table with a teeny-tiny identity column to give you the idea....

IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'Overflow')
    CREATE TABLE [dbo].[Overflow]
    (
        [Tester] [TINYINT] IDENTITY(1, 1) NOT NULL,
        [TestValue] VARCHAR(20) NULL
    ) ON [PRIMARY];
GO

--and fill 'er up pretty high

DECLARE @i INT = 0;

WHILE @i < 232
BEGIN

    INSERT INTO dbo.Overflow
    (
        TestValue
    )
    VALUES
    ('Testing...Testing..');

    SET @i = @i + 1;

END;


--Okay.  Now to find the perpetrator!

CREATE TABLE #t
(
    DatabaseName sysname,
    table_name sysname,
    columnname sysname,
    last_value BIGINT,
    identity_value BIGINT,
    PercentFull INT
);



EXEC sp_MSforeachdb 'USE [?]; 
					 WITH    CTE_1
							  AS ( SELECT   OBJECT_NAME(a.Object_id) AS table_name,
											a.Name AS columnname,
											CONVERT(BIGINT, ISNULL(a.last_value, 0)) AS last_value,
											CASE WHEN b.name = ''tinyint'' THEN 255
												 WHEN b.name = ''smallint'' THEN 32767
												 WHEN b.name = ''int'' THEN 2147483647
												 WHEN b.name = ''bigint'' THEN 9223372036854775807
											END AS identity_value
								   FROM     sys.identity_columns a
											INNER JOIN sys.types AS b ON a.system_type_id = b.system_type_id),
							CTE_2
							  AS ( SELECT   ''?'' AS DatabaseName, table_name, columnname,  last_value, identity_value,
											CONVERT(NUMERIC(18, 2), ( ( CONVERT(FLOAT, last_value) / CONVERT(FLOAT, identity_value) ) * 100 )) AS PercentFull
								   FROM     CTE_1)
                      INSERT INTO #t
					  SELECT  *
					  FROM    CTE_2
					  WHERE   PercentFull >= 90;';


SELECT *
FROM #t
WHERE PercentFull > 0;

--Cleanup

DROP TABLE #t;

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'Sandbox';
GO
USE [master];
GO

DROP DATABASE [Sandbox];
GO

Here is the output:

Copy the code from below the “Okay. Now to find the perpetrator” comment until you get to the “Cleanup” comment.  Put this in a job to run however frequently you need (it ran sub-second on my server), configure an alert to let you know when it gives a positive result, and you should be much happier with your tables!

If you must have an overflow, this is soooo much cuter…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s