Here’s a stored procedure I put together a week or so ago that I thought others might find useful.
We had an issue which should really never be allowed to happen (but as they say, “shit happens”); a rather important table ran out of integer values for the identity column. Luckily the actual value of the ID column on this table isn’t important because the table only stores the most recent month’s data, and everything before that is archived – so we could simply reseed the identity value back to 0 (which had long since been purged from this table).
To prevent this in future I put together a pretty simple query to check all identity values, and check them against the maximum value for their given data types. This eventually became a procedure which is now just part of my “utility” database which I deploy to every production server.
IF OBJECT_ID('dbo.usp_check_max_identity_value') IS NULL
EXEC('CREATE PROC dbo.usp_check_max_identity_value AS ');
GO
ALTER PROC dbo.usp_check_max_identity_value (
@alert_threshold TINYINT = 60, -- The percentage of the max value over which we will alert.
@send_alert_as_email BIT = 0, -- Do you want to send any alerts as an email? (Well... do ya, punk?)
@alert_email_recipients NVARCHAR(MAX) = NULL -- Semicolon-delimited list of email recipients.
) AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @srv_name VARCHAR(200),
@db_name VARCHAR(200),
@table_name VARCHAR(200),
@column_name VARCHAR(200),
@data_type VARCHAR(200),
@max_value BIGINT,
@max_tinyint TINYINT,
@max_smallint SMALLINT,
@max_int INT,
@max_bigint BIGINT,
@cmd NVARCHAR(4000),
@percent_of_max INT,
@alert_subject NVARCHAR(255),
@alert_body NVARCHAR(MAX),
@alert_counter INT;
DECLARE @tt_alert_data TABLE (
row_id INT IDENTITY,
[db_name] VARCHAR(200),
table_name VARCHAR(200),
column_name VARCHAR(200),
data_type VARCHAR(200),
percent_of_max INT
);
-- These are the max values of each integer data type
SELECT @max_tinyint = 255,
@max_smallint = 32767,
@max_int = 2147483647,
@max_bigint = 9223372036854775807;
-- Get a list of all available user databases to loop through
DECLARE db_cursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR
SELECT DISTINCT
QUOTENAME(name)
FROM sys.databases
WHERE database_id > 4
AND is_read_only = 0
AND state_desc = 'ONLINE'
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @db_name;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Get a list of all user tables in the current database
SET @cmd = N'
DECLARE tables_cursor CURSOR GLOBAL STATIC FORWARD_ONLY READ_ONLY FOR
SELECT DISTINCT
QUOTENAME(SCHEMA_NAME(t.[schema_id])) + ''.'' + QUOTENAME(t.name) AS table_name,
QUOTENAME(c.name) AS column_name,
typ.name AS data_type
FROM ' + @db_name + '.sys.tables t
JOIN ' + @db_name + '.sys.columns c ON t.[object_id] = c.[object_id]
JOIN ' + @db_name + '.sys.types typ ON c.system_type_id = typ.system_type_id
WHERE t.[type] = ''U''
AND t.is_ms_shipped = 0
AND c.is_identity = 1
AND typ.name IN (''tinyint'',''smallint'',''int'',''bigint'')
ORDER BY QUOTENAME(SCHEMA_NAME(t.[schema_id])) + ''.'' + QUOTENAME(t.name);
';
EXEC sp_executesql @cmd, N'';
-- Loop through the list of tables
OPEN tables_cursor;
FETCH NEXT FROM tables_cursor INTO @table_name, @column_name, @data_type;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @max_value = NULL, @percent_of_max = NULL;
SET @cmd = N'SELECT @max = MAX(' + @column_name + ') FROM ' + @db_name + '.' + @table_name + ' WITH (NOLOCK);';
-- Run the SELECT to fetch the maximum value from the IDENTITY column
EXEC sp_executesql @cmd,
N'@max BIGINT OUTPUT',
@max = @max_value OUTPUT;
IF @max_value IS NULL GOTO FetchNext;
-- Work out the percentage this represents of the maximum posible value
SELECT @percent_of_max = CAST(CASE
WHEN @data_type = 'tinyint' THEN ((CAST(@max_value AS FLOAT) / CAST(@max_tinyint AS FLOAT)) * 100.0)
WHEN @data_type = 'smallint' THEN ((CAST(@max_value AS FLOAT) / CAST(@max_smallint AS FLOAT)) * 100.0)
WHEN @data_type = 'int' THEN ((CAST(@max_value AS FLOAT) / CAST(@max_int AS FLOAT)) * 100.0)
WHEN @data_type = 'bigint' THEN ((CAST(@max_value AS FLOAT) / CAST(@max_bigint AS FLOAT)) * 100.0)
END AS INT)
-- If the percentage is greater than our threshold, raise an alert
IF (@percent_of_max > @alert_threshold)
BEGIN
-- Insert the values for use later (if we're going to send an email)
IF (@send_alert_as_email = 1)
BEGIN
INSERT @tt_alert_data ([db_name], table_name, column_name, data_type, percent_of_max )
VALUES (@db_name, @table_name, @column_name, @data_type, @percent_of_max);
END
RAISERROR(
'%s - Identity column %s on table %s is currently at %i%% of an %s''s maximum value.',
10,
1,
@db_name,
@column_name,
@table_name,
@percent_of_max,
@data_type
) WITH LOG;
SET @alert_counter = ISNULL(@alert_counter,0) + 1;
END
FetchNext:
FETCH NEXT FROM tables_cursor INTO @table_name, @column_name, @data_type;
END
CLOSE tables_cursor;
DEALLOCATE tables_cursor;
FETCH NEXT FROM db_cursor INTO @db_name;
END
CLOSE db_cursor;
DEALLOCATE db_cursor;
-- If we want to send an alert email (and there is one to send), then let's do that...
IF (@send_alert_as_email = 1) AND (ISNULL(@alert_counter, 0) > 0)
BEGIN
-- If we haven't provided an email recipient, just carry on regardless
IF @alert_email_recipients IS NULL RETURN;
SET @srv_name = QUOTENAME(CAST(@@SERVERNAME AS VARCHAR(200)));
SET @alert_subject = @srv_name + ' - Identity value overflow warning - ' + CAST(@alert_counter AS VARCHAR) + ' columns found.';
-- Build the email body (HTML tags for the benefit of making the email pretty, but not necessary)
set @alert_body = '<html><body><style type="text/css"> h3, p, table {font-family:verdana;}</style>';
set @alert_body = @alert_body +
N'<H3>Identity value overflow warning</H3>' +
N'<p>This alert means that the following tables on the server ' + @srv_name
+ ' have integer-based IDENTITY column values which are currently greater than <strong>'
+ CAST(@alert_threshold AS VARCHAR) + '%</strong> of their respective data-types'' maximum possible values.</p>' +
N'<p>This is just a warning, but you may want to consider increasing the size of the data-type you use (if possible) '
+ 'or reseeding the table (if the IDENTITY value itself is not important, other than it being unique).</p>' +
N'<table border="1" cellspacing="0" cellpadding="3">' +
N'<tr style="background-color:#c9cfff;"><th>Database</th><th>Table</th><th>Column</th><th>Type</th><th>%</th></tr>' +
CAST ( ( SELECT td = [db_name], '',
td = [table_name], '',
td = [column_name], '',
td = [data_type], '',
td = [percent_of_max]
FROM @tt_alert_data
ORDER BY row_id
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)) +
N'</table></body></html>';
-- Send the email (NB: This assumes that you have a default Database Mail profile configured)
EXEC msdb.dbo.sp_send_dbmail
@recipients = @alert_email_recipients,
@subject = @alert_subject,
@body = @alert_body,
@body_format = 'HTML',
@importance = 'High';
END
END
GO
It’s not very complex – it iterates through all user databases on the server, and then over all user tables that have an identity column, and simply selects the maximum values from each of those identity columns. This max value is then compared to the max value for the respective data type of the column (i.e. tinyint, smallint, int, or bigint).
What it does after that is up to you. In this case I send an email with a nicely formatted HTML email displaying the offending columns. Obviously you can customise what action to take, and what your threshold is. And yes, the title of this post is a little misleading since this doesn’t actually prevent anything, but rather alerts you to impending doom.
Warning: Before running this on a production system, make sure that you’re happy for it to be selecting from all of your (potentially large and/or busy) tables that have identity columns. If you have parallel test/dev systems, then run them there instead to prevent any negative performance impact in production. I also take no responsibility for bugs or unexpected behaviour. Consult your GP before taking.
Cheers
DB Dave