Incremento de identidade está saltando no database do SQL Server

Em uma das tabelas, a Fee na coluna “ReceiptNo” no incremento de identidade do database do SQL Server 2012 subitamente começou a saltar para 100s em vez de 1, dependendo das duas coisas a seguir.

  1. se for 1205446 é salto para 1206306, se é 1206321, salta para 1207306 e se é 1207314, salta para 1208306. O que eu quero fazer você notar é que os últimos três dígitos permanecem constantes ou seja, 306 sempre que o salto ocorre conforme mostrado na figura a seguir.

  2. esse problema ocorre quando eu reiniciar meu computador

insira a descrição da imagem aqui

Você provavelmente está encontrando o problema aqui (máquina de wayback).

O SQL Server 2012 agora usa um tamanho de cache de 1.000 ao alocar valores IDENTITY em uma coluna int e reiniciar o serviço pode “perder” valores não utilizados (o tamanho do cache é 10.000 para bigint / numeric ).

A partir dos dados que você mostrou, parece que isso aconteceu após a input de dados para 22 de dezembro, quando reiniciou o SQL Server, reservou os valores 1206306 - 1207305 . Após a input de dados para 24 a 25 de dezembro foi feita outra reboot e o SQL Server reservou o próximo intervalo 1207306 - 1208305 visível nas inputs para o dia 28.

A menos que você esteja reiniciando o serviço com frequência incomum, qualquer valor “perdido” provavelmente não fará diferença significativa no intervalo de valores permitido pelo tipo de dados, portanto, a melhor política é não se preocupar com isso.

Se, por algum motivo, isso for um problema real, consulte as soluções alternativas no encadeamento vinculado do Item do Connect.

  1. Você pode usar uma SEQUENCE vez de uma coluna de identidade e definir um tamanho de cache menor, por exemplo, e usar NEXT VALUE FOR em um padrão de coluna.
  2. Ou aplique o sinalizador de rastreamento 272, que torna a alocação de IDENTITY registrada como nas versões anteriores.

Você deve estar ciente de que essas soluções não garantem nenhuma lacuna. Isso nunca foi garantido pela IDENTITY , pois só seria possível por meio da serialização de inserções na tabela. Se você precisar de uma coluna sem intervalos, precisará usar uma solução diferente da IDENTITY ou da SEQUENCE

Esse problema ocorre após reiniciar o SQL Server.

A solução é:

  • Execute o SQL Server Configuration Manager .

  • Selecione o SQL Server Services .

    Gerenciador de configuração do SQL Server

  • Clique com o botão direito do mouse em SQL Server e selecione Propriedades .

  • Na janela de abertura, em parameters de boot , digite -T272 e clique em Adicionar . Em seguida, pressione o botão Aplicar e reinicie.

    Parâmetros de inicialização do SQL Server

Eu sei que minha resposta pode estar atrasada para a festa. Mas eu resolvi de outra maneira, adicionando um procedimento armazenado de boot no SQL Server 2012.

Crie um procedimento armazenado a seguir no database mestre.

 USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[ResetTableNameIdentityAfterRestart] AS BEGIN begin TRAN declare @id int = 0 SELECT @id = MAX(id) FROM [DatabaseName].dbo.[TableName] --print @id DBCC CHECKIDENT ('[DatabaseName].dbo.[TableName]', reseed, @id) Commit END 

Em seguida, adicione-o ao Start up usando a seguinte syntax.

 EXEC sp_procoption 'ResetOrderIdentityAfterRestart', 'startup', 'on'; 

Essa é uma boa ideia se você tiver poucas tabelas. mas se você tiver que fazer por muitas tabelas, esse método ainda funciona, mas não é uma boa ideia.

A partir do SQL Server 2017+ você pode usar ALTER DATABASE SCOPED CONFIGURATION :

IDENTITY_CACHE = {ON | FORA }

Ativa ou desativa o cache de identidade no nível do database. O padrão é ON. O cache de identidade é usado para melhorar o desempenho do INSERT em tabelas com colunas de identidade. Para evitar lacunas nos valores da coluna Identidade nos casos em que o servidor reinicia inesperadamente ou efetua failover para um servidor secundário, desative a opção IDENTITY_CACHE. Essa opção é semelhante ao Sinalizador de rastreamento do SQL Server 272 existente, exceto pelo fato de que ele pode ser definido no nível do database, e não apenas no nível do servidor.

(…)

G. Definir IDENTITY_CACHE

Este exemplo desativa o cache de identidade.

 ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ; 

Este ainda é um problema muito comum entre muitos desenvolvedores e aplicativos, independentemente do tamanho.

Infelizmente, as sugestões acima não corrigem todos os cenários, ou seja, hospedagem compartilhada, você não pode confiar em seu host para definir o parâmetro de boot -t272.

Além disso, se você tiver tabelas existentes que usam essas colunas de identidade para chaves primárias, é um grande esforço descartar essas colunas e recriar novas para usar a solução alternativa da sequência BS. A solução alternativa Seqüência só é boa se você estiver projetando as tabelas novas do zero no SQL 2012+

Bottom line é, se você estiver no Sql Server 2008R2, então FIQUE NELA. Sério, fique nisso. Até a Microsoft admitir que eles introduziram um bug ENORME, que ainda está lá mesmo no Sql Server 2016, então não devemos atualizar até que eles possuam e CORRECAM o TI.

A Microsoft apresentou uma mudança, ou seja, eles quebraram uma API que não funciona mais como projetada, devido ao fato de seu sistema esquecer sua identidade atual em uma reboot. Cache ou sem cache, isso é inaceitável, eo desenvolvedor da Microsoft com o nome de Bryan precisa possuí-lo, em vez de dizer ao mundo que é “por design” e um “recurso”. Claro, o cache é um recurso, mas perder a noção do que a próxima identidade deve ser, NÃO É UM RECURSO. É um fricken BUG !!!

Compartilharei a solução alternativa que usei, porque Meus bancos de dados estão em servidores de hospedagem compartilhada, também, não estou descartando e recriando minhas colunas de chave primária, isso seria um enorme PITA.

Em vez disso, este é o meu vergonhoso hack (mas não tão vergonhoso como este bug POS que a Microsoft introduziu).

Hack / Fix:

Antes dos comandos de inserção, apenas distribua sua identidade antes de cada inserção. Essa correção é recomendada apenas se você não tiver controle administrativo sobre sua instância do Sql Server, caso contrário, sugiro que a nova propagação seja reiniciada no servidor.

 declare @newId int -- where int is the datatype of your PKey or Id column select @newId = max(YourBuggedIdColumn) from YOUR_TABLE_NAME DBCC CheckIdent('YOUR_TABLE_NAME', RESEED, @newId) 

Apenas aquelas 3 linhas imediatamente antes da sua inserção, e você deve estar pronto para ir. Isso realmente não afetará muito o desempenho, ou seja, será imperceptível.

Boa sorte.

Existem muitas razões possíveis para saltar valores de identidade. Eles variam de inserções recuperadas a gerenciamento de identidade para replicação. O que está causando isso no seu caso eu não posso dizer sem gastar algum tempo no seu sistema.

Você deve saber, no entanto, que em nenhum caso você pode assumir que uma coluna de identidade seja contígua. Há muitas coisas que podem causar lacunas.

Você pode encontrar um pouco mais de informações sobre isso aqui: http://sqlity.net/pt/792/the-gap-in-the-identity-value-sequence/