I still do not know how those values changed but i modified an existing T-SQL script that I found via Google so I can proactively check for the same issue and fix before the error pops up again, breaking our application.
Here's the code:
USE master;
DECLARE @SQLSearch varchar(1000), @CurID int, @MaxID int, @CurSQL varchar(max)
DECLARE @t TABLE (ID int identity(1,1), sqlstring varchar(max))
insert into @t
select '
USE [' + name + '];
DECLARE @SchemaName nvarchar(128),
@TableName nvarchar(128),
@ColumnName nvarchar(128)
DECLARE @MyTableVar table(
ID bigint IDENTITY (1,1) PRIMARY KEY CLUSTERED,
SchemaName nvarchar(128),
TableName nvarchar(128),
ColumnName nvarchar(128),
object_id int,
last_value sql_variant,
Count bigint,
LastUsedIDValue bigint,
CountDifference bigint,
LastIDDifference bigint,
GeneratedDBCCCode nvarchar(max),
MaxID bigint
)
INSERT INTO @MyTableVar
(SchemaName, TableName, ColumnName, object_id, last_value, Count, LastUsedIDValue, CountDifference, LastIDDifference)
SELECT SCHEMA_NAME(schema_id) AS SchemaName, T1.name AS TableName, T2.name AS ColumnName, T2.object_id,
last_value, T3.Rows AS Count, ident_current(SCHEMA_NAME(schema_id) + ''.'' + ''['' + T1.name + '']'') AS LastUsedIDValue,
CAST(last_value AS bigint) - T3.Rows AS CountDifference, CAST(last_value AS bigint) - CAST(ident_current(SCHEMA_NAME(schema_id) + ''.'' + ''['' + T1.name + '']'') AS bigint) AS LastIDDifference
FROM sys.tables AS T1
INNER JOIN sys.identity_columns AS T2
ON T1.object_id = T2.object_id
INNER JOIN dbo.SysIndexes AS T3
ON T3.ID = T1.object_ID
WHERE TYPE_DESC = ''USER_TABLE''
AND is_identity = 1
AND T3.IndID < 2
AND OBJECTPROPERTY(T1.object_ID,''IsMSShipped'') = 0
DECLARE @CurrentTableVarID bigint = 0,
@MaxTableVarID bigint = 0,
@CounterCheck bigint = 0,
@SQLString nvarchar(max),
@ParameterDefinition nvarchar(500),
@MaxID bigint,
@MaxIDOut bigint
SELECT @MaxTableVarID = Max(ID) FROM @MyTableVar GROUP BY ID ORDER BY ID ASC
SELECT @CurrentTableVarID =Max(ID) FROM @MyTableVar GROUP BY ID ORDER BY ID DESC
WHILE @CurrentTableVarID <= @MaxTableVarID
BEGIN
SELECT @SchemaName = SchemaNAme, @TableName = TableName,
@ColumnName = ColumnName
FROM @MyTableVar
WHERE ID = @CurrentTableVarID
SET @ParameterDefinition = ''@MaxIDOut bigint OUTPUT'';
SET @SQLString = ''SELECT @MaxIDOut = Max('' + @ColumnName + '') FROM ['' + @SchemaName + ''].['' + @TableName + ''] GROUP BY '' + @ColumnName + '' ORDER BY '' + @ColumnName + '' ASC''
EXEC sp_executesql @SQLString, @ParameterDefinition, @MaxIDOut = @MaxID OUTPUT
UPDATE @MyTableVar
SET MaxID = @MaxID
WHERE ID = @CurrentTableVarID
/*
INSERT INTO @MaxIDTableVar
(ParentID, MaxID)
EXEC [dbo].[ReturnColumnMaxSP]
@SchemaName = @SchemaName,
@TableName = @TableName,
@ColumnName = @ColumnName,
@ParentID = @CurrentTableVarID
*/
SET @CounterCheck = @CounterCheck + 1
SET @CurrentTableVarID = @CurrentTableVarID + 1 -- increment the loop
END
SELECT [DatabaseName] = ''' + name + ''', SchemaName, TableName, ColumnName, last_value, LastUsedIDValue, MaxID, CAST(last_value AS bigint) - MaxID AS MaxIDDifference,
''DBCC CHECKIDENT ('''''' + SchemaName + ''.'' + TableName + '''''' , RESEED, '' + CAST(MaxID AS nvarchar(50)) + '')'' AS GeneratedDBCCCode
FROM @MyTableVar
WHERE CAST(last_value AS bigint) - MaxID < 0
ORDER BY MaxIDDifference DESC
'
from sysdatabases
where name not in ('master','model','msdb')
SELECT @CurID = Min(ID), @MaxID = Max(ID)
FROM @t
-- Looping across databases
WHILE @CurID <= @MaxID
BEGIN
SET @CurSQL = (SELECT sqlstring from @t where ID = @CurID)
EXEC (@CurSQL)
SET @CurID = @CurID + 1
END
I tested on my VMware environment and it worked. I recommend doing the same and test before run it on production. If anyone can improve, feel free to do it. My T-SQL skills are not so good as others, that's for sure.
I ran above code on production, changing the NOT IN to IN , and checking one database. It takes a while depending of the database size but will identify any seed issue in advance,