SQL: Pula 1000 numeros na sequencia da tabela quando tem queda de energia ou servidor reinicia.
Software: SOFTSHOP | Grupo: SOFTSHOP > ANALISE | Prioridade: ALTA
Solução
Causa:
O SQL Server perde a auto numeração de um indice.
Solução:
1 - Executa a função abaixo caso seja SQL Server 2014 com versão anterior ao Service Pack 2 (Compilação 12.0.2269):
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_FixSeeds2012] Script Date: 23/05/2017 09:12:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[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 '+@DatabaseName + '
--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 ''['+@DatabaseName+'].[''+@tableName+''].[''+@schemaName+''].[''+@columnName+'']''
EXEC (''declare @MAX int = 0
select @MAX = max("''+@columnName+''") from ['+@DatabaseName+'].[''+@schemaName+''].[''+@tableName+'']
if (@MAX IS NULL)
BEGIN
SET @MAX = 0
END
DBCC CHECKIDENT(['+@DatabaseName+'.''+@schemaName+''.''+@tableName+''],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
OBS: FAQ NAO VOLTA A NUMERAÇÃO APENAS IMPEDE QUE OCORRA O SALTO DE NUMERAÇÃO NOVAMENTE.
Tags: pular numeracao, auto numeracao, reseed, sql server, pula, 1000