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.
