public
Created

SQL Server IDENTITY overflow check

  • Download Gist
identity overflow check.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195
-- Author: Nicholas Chammas
-- Turbine / WB Games
-- 2011/10/13
-- Inspired by: http://vyaskn.tripod.com/sql_server_check_identity_columns.htm
-- Purpose: Find all IDENTITY columns on all databases on this instance
-- and show how much of their range they have exhausted.
--
-- CAVEATS
--
-- 1. Developed using SQL Server 2008 syntax.
--
-- To port to SQL Server 2005:
-- * split out variable assignments into separate SET statements
--
-- To port to SQL Server 2000:
-- * perform all steps required to port to SQL Server 2005
-- * convert CTE into derived table
-- * replace the 2005+ sys.catalog_views with SQL Server 2000 equivalents
-- (don't know how much of the script will have to be rewritten to do this)
--
-- 2. This script will not catch identity columns that are about to overflow
-- due to ridiculously large identity increments. For example, a tinyint
-- column with a current identity value of 50 is about 50/255 ~= 20% full.
-- However, this column will overflow a LOT faster if the increment is 50
-- (which gives you 4 more inserts) as opposed to 1 (which gives you 205
-- more inserts).
--
-- NOTE:
-- gist makes the spacing look all messed up. If you copy the raw code and
-- paste it into SQL Server Management Studio everything should look good.
 
SET NOCOUNT ON;
 
DECLARE @fullness_threshold DECIMAL(4, 1) = 80.0;
DECLARE @identity_query NVARCHAR(MAX);
 
CREATE TABLE #identity_columns (
[database] [sysname] NOT NULL
, [schema] [sysname] NOT NULL
, [table] [sysname] NOT NULL
, [column] [sysname] NOT NULL
, [data_type] [sysname] NOT NULL
, [precision] [tinyint] NOT NULL
, [identity_value] [decimal](38,0) NOT NULL
, [identity_increment] [decimal](38,0) NOT NULL
, [identity_limit] [float] NOT NULL
, [percentage_used] [decimal](4, 1) NOT NULL
-- only one IDENTITY column allowed per table
, PRIMARY KEY (
[database]
, [schema]
, [table]
)
);
 
DECLARE db_cursor CURSOR FAST_FORWARD FOR
SELECT QUOTENAME(name)
FROM sys.databases
WHERE name NOT IN (
'tempdb'
);
 
-- query to pull identity columns and calculate their fullness
-- copied from separate query file
SET @identity_query = '
DECLARE @MAX_TINYINT TINYINT = 255;
DECLARE @MAX_SMALLINT SMALLINT = 32767;
DECLARE @MAX_INT INT = 2147483647;
DECLARE @MAX_BIGINT BIGINT = 9223372036854775807;
 
DECLARE @MIN_TINYINT TINYINT = 0;
DECLARE @MIN_SMALLINT SMALLINT = -32768;
DECLARE @MIN_INT INT = -2147483648;
DECLARE @MIN_BIGINT BIGINT = -9223372036854775808;
 
WITH identity_columns AS (
SELECT
DB_NAME() AS [database]
, SCHEMA_NAME(t.schema_id) AS [schema]
, t.name AS [table]
, c.name AS [column]
, typ.name AS [data_type]
, c.precision AS [precision]
-- , c.scale AS [scale] -- is always 0 for IDENTITY columns
, IDENT_SEED(SCHEMA_NAME(t.schema_id) + ''.'' + t.name) AS [identity_seed]
, IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + ''.'' + t.name) AS [identity_value]
, IDENT_INCR(SCHEMA_NAME(t.schema_id) + ''.'' + t.name) AS [identity_increment]
, CASE
WHEN typ.name = ''bigint'' THEN @MIN_BIGINT
WHEN typ.name = ''int'' THEN @MIN_INT
WHEN typ.name = ''smallint'' THEN @MIN_SMALLINT
WHEN typ.name = ''tinyint'' THEN @MIN_TINYINT
WHEN typ.name IN (
''decimal''
, ''numeric''
) THEN -POWER(1e1, typ.precision) + 1
END AS [identity_min]
, CASE
WHEN typ.name = ''bigint'' THEN @MAX_BIGINT
WHEN typ.name = ''int'' THEN @MAX_INT
WHEN typ.name = ''smallint'' THEN @MAX_SMALLINT
WHEN typ.name = ''tinyint'' THEN @MAX_TINYINT
WHEN typ.name IN (
''decimal''
, ''numeric''
) THEN POWER(1e1, typ.precision) - 1
END AS [identity_max]
, CASE
-- casting to larger type so the intermediate result can fit; arithmetic overflow otherwise
WHEN typ.name = ''bigint'' THEN CAST(@MAX_BIGINT AS NUMERIC(38, 0)) - @MIN_BIGINT
WHEN typ.name = ''int'' THEN CAST(@MAX_INT AS BIGINT) - @MIN_INT
WHEN typ.name = ''smallint'' THEN CAST(@MAX_SMALLINT AS INT) - @MIN_SMALLINT
WHEN typ.name = ''tinyint'' THEN CAST(@MAX_TINYINT AS SMALLINT) - @MIN_TINYINT
WHEN typ.name IN (
''decimal''
, ''numeric''
) THEN 2 * POWER(1e1, typ.precision) - 2
END AS identity_range_size
FROM
sys.columns AS c
INNER JOIN
sys.tables AS t
ON t.[object_id] = c.[object_id]
INNER JOIN
sys.types AS typ
-- not system_type_id; user-defined types mapped to system types
-- create duplicate sys.types records when you join on system_type_id
ON c.user_type_id = typ.user_type_id
WHERE
c.is_identity = 1
)
SELECT
[database]
, [schema]
, [table]
, [column]
, [data_type]
, [precision]
, [identity_value]
, [identity_increment]
, CASE SIGN([identity_increment])
WHEN -1 THEN [identity_min]
ELSE [identity_max]
END AS [identity_limit]
, CAST(
([identity_value] - [identity_min])
/ [identity_range_size]
* 100
AS DECIMAL(4, 1)) AS [percentage_used]
FROM
identity_columns
;
';
 
DECLARE @db_name SYSNAME;
 
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @db_name;
 
-- run identity overflow check on each database
-- and collect results in table variable
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #identity_columns
EXECUTE ('USE ' + @db_name + '; ' + @identity_query);
FETCH NEXT FROM db_cursor INTO @db_name;
END;
 
CLOSE db_cursor;
DEALLOCATE db_cursor;
 
DECLARE @problem_columns INT = (
SELECT COUNT(*)
FROM #identity_columns
WHERE percentage_used >= @fullness_threshold
);
 
SELECT *
FROM #identity_columns
WHERE [percentage_used] >= @fullness_threshold
ORDER BY
[percentage_used] DESC
, [database] ASC
, [schema] ASC
, [table] ASC
, [column] ASC
;
 
DROP TABLE #identity_columns;

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.