Checking for High Identity Counts Across A Database
Recently we had an issue with some 3rd part databases that had hit the max INT on some of the identity columns. Potentially the fix for this is easy enough, but you have to be able to anticipate how the application will deal with resetting the identity value back down.
I’ve blogged about resetting identity fields before, as well as truncating and deleting and there is plenty on the web about resetting identity fields. But hitting the maximum int number on a live database in production is never a good thing, particularly if you don’t own the development of the database in-house. So I needed to find a way to monitor any columns close to hitting this value so I could contact the vendor and resolve without causing chaos.
So I created a job that runs the script below on each database I need to check.It’s a very light script: it runs on a 1tb database with over 3000 idents in just under 20 seconds. It finds all the tables with identity columns then runs IDENT_CURRENT on the table.If they hit a threshold then an email is sent to a distribution list.
You can set the frequency of the job, but I recommend running it weekly and setting the threshold of the exceeding value according to your workload. In this case the threshold is 2 billion, so unless any of your tables have more than 100 million inserts a week this will be fine.
-- Email settings
CREATE PROCEDURE dbo.test
DECLARE @profile_name VARCHAR(100) = '_Profile_Mail'
DECLARE @strsubject VARCHAR(100)
DECLARE @tableHTML NVARCHAR(max)
DECLARE @EmailRecipients VARCHAR(max) = '[email protected]'
CREATE table #IdentTable (iServerName SYSNAME, iDatabaseName SYSNAME, iTableName SYSNAME)
CREATE table #CheckIdentResults (ServerName SYSNAME, DatabaseName SYSNAME, TableName SYSNAME, CurrentIdentity INT)
INSERT INTO #IdentTable
SELECT
@@SERVERNAME AS _ServerName,
DB_NAME(DB_ID()) AS _DatabaseName,
(s.name +'.'+ t.name) AS _Name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
ON s.[schema_id] = t.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.identity_columns
WHERE [object_id] = t.[object_id]
)
DECLARE @COUNT INT
DECLARE @TblName SYSNAME
DECLARE @SrvrName SYSNAME
DECLARE @DbName SYSNAME
DECLARE @SQL NVARCHAR (1000) = ''
SELECT @COUNT = COUNT (*) FROM #IdentTable
WHILE @COUNT > 0
BEGIN
SELECT @TblName = MAX (iTableName) FROM #IdentTable
SELECT @SrvrName = i.iServerName FROM #IdentTable i WHERE i.iTableName = @TblName
SELECT @DbName = i.iDatabaseName FROM #IdentTable i WHERE i.iTableName = @TblName
SET @SQL = 'DECLARE @myCurrentSeedValue int
SELECT @myCurrentSeedValue = IDENT_CURRENT( '''+@TblName+''')'+
'
IF @myCurrentSeedValue >=2000000000
INSERT INTO #CheckIdentResults (ServerName, DatabaseName, TableName, CurrentIdentity) VALUES ( '''+@SrvrName+''', '''+ @DbName+''''+','+'''' + @TblName+''', @myCurrentSeedValue)'
--PRINT (@SQL)
EXEC (@SQL)
DELETE FROM #IdentTable where iTableName = @TblName
SET @COUNT = @COUNT - 1
END
IF EXISTS (
SELECT * FROM #CheckIdentResults
)
BEGIN
SELECT @strsubject = 'HighMax Ident Count ' + convert(VARCHAR(17), getdate(), 113) + ' ***'
SET @tableHTML = N'<H1>High Max IDENT Count on Table</H1>' + N'<h3>The following tables have high IDENT counts</h3>' + N'<table border="1">' + N'<tr><th>Server</th>' + N'<th>Database</th>' + N'<th>Table</th>' + N'<th>Ident Count</th>' + '</tr>' + cast((
SELECT td = CIR.ServerName
,''
,td = CIR.DatabaseName
,''
,td = CIR.TableName
,''
,td = CIR.CurrentIdentity
,''
FROM #CheckIdentResults CIR
FOR XML path('tr')
,type
) AS NVARCHAR(max)) + N'</table>' + CHAR(10) + CHAR(13) + '<p> Please contact 3rd party vendor'
EXEC msdb.dbo.sp_send_dbmail @recipients = @EmailRecipients
,@subject = @strsubject
,@body = @tableHTML
,@body_format = 'HTML'
,@profile_name = @profile_name
END
DROP TABLE #IdentTable
DROP TABLE #CheckIdentResults