zsx's Old Blog

黑历史逐步隐藏中_(:з」∠)_仅保留部分可能有些价值的文章,并不保证将来的可访问性。

SQL Server 2012 IDENTITY自增列自动编号自动加1000


这几天给人转换数据库,总碰到这种现象:

image

以中文在百度和Google查找,均无结果。

使用关键词:

“sql server IDENTITY  1000 WHY”(有点坑爹吧),找到了:


楼主问:

When a table with less than 1000 rows that has an identity value is part of a database that is failed over in an AlwaysOn availability group, the identity is reseeded to 1000. If the identity value is already over 1000, no reseed occurs.

This also occurs if you restart the server.

底下,Microsoft有给出回答,但只是官话——

Thanks for your feedback.

We're trying to quickly assess the impact of this issue.

Could people please reply indicating if they're hitting it failing over Availability Groups or doing something else (e.g. restarting server)?

重启你妹啊!!评估你妹啊!!


我把几个人的解决代码贴到这里,方便各位:



解决方案1(By Leo.G.Miller):

Enable "scan for startup procs configuration.
In Master add one or more stored procs as required to reseed each table based on the code here.

create proc FixIdent_DBName_Table
as
begin
declare @MAX int
select @MAX = max(Identity_Col) from DBName.dbo.Table
DBCC CHECKIDENT([DBName.dbo.Table],RESEED,@MAX)
end
      
sp_procoption @ProcName = 'FixIdent_DBName_Table'
    , @OptionName = 'startup'
    , @OptionValue = 'true'


After the restart the seed will be reset correctly.

Leo Miller
Senior DBA Consultent
SQL Services Limited
New Zealand


解决方案2(By GordonBeeming):

A full instance workaround would be to use the code below. It uses the same principal as Leo.G.Miller mentioned just for automatically does every identity column in the SQL instance

--------------------------------------------------------------------------------

USE master; 
GO
CREATE PROCEDURE sp_FixSeeds2012
AS
BEGIN
      
    --foreach database
    DECLARE @DatabaseName varchar(255)
          
    DECLARE DatabasesCursor CURSOR READ_ONLY
    FOR
        SELECT name
        FROM sys.databases
        where name not in ('master','tempdb','model','msdb') and sys.databases.state_desc = 'online'
      
    OPEN DatabasesCursor
      
    FETCH NEXT FROM DatabasesCursor
    INTO @DatabaseName
      
    WHILE @@FETCH_STATUS = 0
    BEGIN
          
        EXEC ('USE '[email protected] + '
      
        --foreach identity column
        DECLARE @tableName varchar(255)
        DECLARE @columnName varchar(255)
        DECLARE @schemaName varchar(255)
          
        DECLARE IdentityColumnCursor CURSOR READ_ONLY
        FOR
              
            select TABLE_NAME , COLUMN_NAME, TABLE_SCHEMA 
            from INFORMATION_SCHEMA.COLUMNS 
            where COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, ''IsIdentity'') = 1 
              
      
        OPEN IdentityColumnCursor
      
        FETCH NEXT FROM IdentityColumnCursor
        INTO @tableName, @columnName, @schemaName
      
        WHILE @@FETCH_STATUS = 0
        BEGIN
              
            print ''['[email protected]+'].[''[email protected]+''].[''[email protected]+''].[''[email protected]+'']'' 
            EXEC (''declare @MAX int = 0
                    select @MAX = max(''[email protected]+'') from ['[email protected]+'].[''[email protected]+''].[''[email protected]+'']
                    if (@MAX IS NULL)
                    BEGIN
                        SET @MAX = 0
                    END
                    DBCC CHECKIDENT(['[email protected]+'.''[email protected]+''.''[email protected]+''],RESEED,@MAX)'')
      
            FETCH NEXT FROM IdentityColumnCursor
            INTO @tableName, @columnName, @schemaName
      
        END
      
        CLOSE IdentityColumnCursor
        DEALLOCATE IdentityColumnCursor')
      
      
      
        FETCH NEXT FROM DatabasesCursor
        INTO @DatabaseName
      
    END
      
    CLOSE DatabasesCursor
    DEALLOCATE DatabasesCursor
END
GO
      
EXEC sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE
GO
EXEC sp_configure 'scan for startup procs', 1 ;
GO
RECONFIGURE
GO
      
      
      
EXEC sp_procoption @ProcName = 'sp_FixSeeds2012'
    , @OptionName = 'startup'
    , @OptionValue = 'true'
GO



解决方案3(By SQLServerSteve):

Here is a cursorless workaround that will provide more information about identity discrepancies in all of your user tables, plus auto-generate the DBCC CHECKIDENT code needed to reseed them all, if necessary. This procedure compares the last_value from sys.identity_columns, the row Count, the results of the IDENT_CURRENT function and the Max value actually in the identity column, for each user table in a given database. If the CountDifference, MaxIDDifference or LastIDValueDifference is around 1,000, it's probably due to the identity bug. You may need to edit it to suit your own needs, as I discuss in greater detail in my blog post at http://multidimensionalmayhem.wordpress.com/2012/10/16/another-stop-gap-solution-for-the-sql-server-2012-identity-crisis/ .


ALTER PROCEDURE [dbo].[ReturnIdentityInfoSP]
AS
BEGIN
    
    
    
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
-- 'DBCC CHECKIDENT (''' + SCHEMA_NAME(schema_id) + '.' + T1.name + ''' , RESEED, ' + CAST(MaxID AS nvarchar(50)) + ')' AS GeneratedDBCCCode
    
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 SchemaName, TableName, ColumnName, object_id, last_value, Count, LastUsedIDValue, MaxID, CountDifference, LastIDDifference, CAST(last_value AS bigint) - MaxID AS MaxIDDifference, 
'DBCC CHECKIDENT (''' + SchemaName + '.' + TableName + ''' , RESEED, ' + CAST(MaxID AS nvarchar(50)) + ')' AS GeneratedDBCCCode
FROM @MyTableVar
ORDER BY MaxIDDifference DESC
END


控制面板
您好,欢迎到访网站!
  查看权限
Google Adsense
文章归档
站点信息
  • 文章总数:259
  • 页面总数:10
  • 分类总数:17
  • 标签总数:136
  • 评论总数:1644
  • 浏览总数:216659

Powered By Z-BlogPHP 1.5.1 Zero

闽ICP备15006942号   闽公网安备 35010302000147号