none
Identity avançou 1000 posições RRS feed

  • Pergunta

  • Boa tarde,

    Temos um clientes nos questionou que um campo que é sequencial (Identity) avançou 1000 posições, porém, analisando não era para ter ocorrido essa situação.

    Alguém já passou por isso?


    Atenciosamente, Ruberlei. www.t-sql.com.br

    quarta-feira, 29 de abril de 2015 19:20

Respostas

  • Ruberlei,

    Isso ocorre quando algum registro é abortado ou quando é feito delete, geralmente o SQL faz um salto no identity.

    http://social.technet.microsoft.com/wiki/pt-br/contents/articles/26286.como-evitar-que-minha-coluna-identity-quebre-a-sequencia-de-valores.aspx


    Se a resposta foi útil por favor classifique. Tiago Neves - @tiagolneves

    • Marcado como Resposta Ruberlei quarta-feira, 29 de abril de 2015 20:29
    quarta-feira, 29 de abril de 2015 20:04

Todas as Respostas

  • Ruberlei, boa tarde.

    Isso provavelmente foi uma transação que inseriu 1000 registros (ou várias transações) e que por algum motivo foi feito um ROLLBACK.

    Mesmo que a transação não seja confirmada (COMMIT) o IDENTITY não volta e isso causa os buracos na tabela.


    Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

    quarta-feira, 29 de abril de 2015 19:30
  • Mariana, essa tabela toda movimentação dela é feita via aplicação e aplicação apenas permite um cadastro por vez e essa opção de transação não se aplica pois nesse cadastro não tem transação.

    Atenciosamente, Ruberlei. www.t-sql.com.br

    quarta-feira, 29 de abril de 2015 19:59
  • Ruberlei,

    Isso ocorre quando algum registro é abortado ou quando é feito delete, geralmente o SQL faz um salto no identity.

    http://social.technet.microsoft.com/wiki/pt-br/contents/articles/26286.como-evitar-que-minha-coluna-identity-quebre-a-sequencia-de-valores.aspx


    Se a resposta foi útil por favor classifique. Tiago Neves - @tiagolneves

    • Marcado como Resposta Ruberlei quarta-feira, 29 de abril de 2015 20:29
    quarta-feira, 29 de abril de 2015 20:04
  • O link que passou tirou todas as minhas dúvidas.

    Obrigado.


    Atenciosamente, Ruberlei. www.t-sql.com.br

    quarta-feira, 29 de abril de 2015 20:29
  • Mariana, essa tabela toda movimentação dela é feita via aplicação e aplicação apenas permite um cadastro por vez e essa opção de transação não se aplica pois nesse cadastro não tem transação.

    Atenciosamente, Ruberlei. www.t-sql.com.br

    Ruberlei, apenas para esclarecer: mesmo que seja feito um a um sem uma transação explicita, o SQL Server transaciona implicitamente comandos de INSERT/UPDATE/DELETE (entre outras ações).

    Logo se pela aplicação ocorrer um erro, o ROLLBACK será feito e o IDENTITY irá ficar com o "buraco".


    Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

    quarta-feira, 29 de abril de 2015 20:35
  • Deleted
    quarta-feira, 29 de abril de 2015 23:10
  • Jose.Diz,

    Concordo com você em relãção as observações no uso do Identity!!!

    Só por questões de esclarecimento, é possível contornar este gap do Identity quando o SQL Server é realizado, podemos fazer uso de um Job que deverá ser rodado sempre que o serviço do SQL Server fora reinicializado, onde o mesmo deverá obter o ultimo número inserido nas respectivas tabelas e definir este número para o Identity.

    Veja este exemplo:

    Declare @Identity Int
    
    ---Refazendo numeração Controle de Entrada ---
    Set @Identity=(Select Ident_Current('CTEntrada'))
    
    DBCC CheckIdent('CTEntrada,Reseed,@Identity)
    
    ---Refazendo numeração Controle de Produção ---
    Set @Identity=(Select Ident_Current('CTProducao_Moinho'))
    
    DBCC CheckIdent('CTProducao',Reseed,@Identity)
    
    ---Refazendo numeração Controle de Entrada - Recebimento  ---
    Set @Identity=(Select Ident_Current('CTEntrada_Recebimento'))
    
    DBCC CheckIdent('CTEntrada_Recebimento',Reseed,@Identity)

    As demais observações são importantes!

    Eu particularmente sou contra o uso da Trace Flag 272, eu prefiro tentar fazer os contornos e ajustes por outros caminhos.

    Ruberlei,

    É importante destacar que se você estiver utilizando o SQL Server 2012, este é um comportamento que já foi reportado e identificado pela própria Microsoft como uma situação normal em versões passadas, mas que se agravou na versão 2012 do SQL Server no momento que o serviço do SQL Server é reinicializado, onde colunas que fazem o uso do Identity são afetadas tendo valor do Identity definido como 1000, por isso é uma opção para contornar este comportamento é o uso Trace Flag 272, conforme recomenda o link:https://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity#details

    Mas vale ressaltar que tanto a Trace Flag 272, como também, o uso do Sequence Generator With NoCache não resolvem o problema, é algo palhativo.


    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]



    sábado, 2 de maio de 2015 00:33
  • Deleted
    sábado, 2 de maio de 2015 08:36
  • Deleted
    sábado, 2 de maio de 2015 10:17
  • Jose.Diz,

    Sim, sim, você esta correto entendi de forma errada, já acertei o post!!!

    Em relação a sua pergunta, então na verdade você vai criar o Job para ser executado assim que o Serviço de Banco de dados seja inicializado, neste caso, entendo que somente após o Database Engine subir, o job será invocado e chamado!!!

    Não entendi quando você se referi a Job generico?


    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 6 de maio de 2015 17:58
  • (...) este é um problema que já foi reportado e identificado pela própria Microsoft como um possível bug ou falha do SQL Server em momentos que o serviço do SQL Server é reinicializado, onde colunas que fazem o uso do Identity são afetadas tendo valor do Identity definido como 1000, (...)

    Pedro, não é um bug mas sim uma característica da forma como o Identity está implementado. Nem mesmo ativar o trace flag 272 resolve, pois esse salto já ocorria nas versões anteriores e foi agravado a partir da versão 2012 do SQL Server.

    O tema foi tratado pela Microsoft no Failover or Restart Results in Reseed of Identity, citado anteriormente, onde a Microsoft apresentou duas soluções de contorno:

    If you require the same identity generation semantics as previous versions of SQL Server there are two options available:

    • Use trace flag 272
      This will cause a log record to be generated for each generated identity value.
      The performance of identity generation may be impacted by turning on this trace flag.
    • Use a sequence generator with the NO CACHE setting
      This will cause a log record to be generated for each generated sequence value.
      Note that the performance of sequence value generation may be impacted by using NO CACHE.


    E a Microsoft reforça que "As documented in books online for previous versions of SQL Server the identity property does not guarantee the absence of gaps". É que as causas do salto no valor da coluna Identity são várias: além da reinicialização do servidor, há outras citadas no artigo The Gap in the Identity Value Sequence.

    Por isso que venho repetindo em vários tópicos correlatos que, se necessitam de uma sequência numérica contínua sem falhas, não usem IDENTITY.


        José Diz     Belo Horizonte, MG - Brasil
    (Se encontrou a solução nesta resposta, ou se o conteúdo foi útil, lembre-se de marcá-la)



    Jose.Diz,

    Ok, ok, mas eu não acho que devemos dizer que o Identity possui falhas, na verdade ele tem um comportamento específico que é criar um sequência numérica de forma continua!!! Este comportamento ao meu ver é algo estranho e sim uma possível falha por parte do SQL Server, na digo bug, mas sim uma falha.


    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 6 de maio de 2015 18:03
  • Bom dia,

    Apenas para constar, fui testar esse cenário no SQL Server 2014 e não ocorreu.

    Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
        Feb 20 2014 20:04:26
        Copyright (c) Microsoft Corporation
        Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

    Meu teste foi inserir 1000 registros reiniciava o SQL server fazia insert de mais 1000 registros reiniciava fiz isso algumas vezes e não ocorreu essa situação.


    Atenciosamente, Ruberlei. www.t-sql.com.br

    sábado, 9 de maio de 2015 14:14
  • Deleted
    sábado, 9 de maio de 2015 20:55
  • Realizei o teste tentando fugir do tamanho do cache também não ocorreu a situação no SQL 2014, irei fazer o mesmo teste em versões anteriores.

    Atenciosamente, Ruberlei. www.t-sql.com.br

    domingo, 10 de maio de 2015 22:48
  • Deleted
    segunda-feira, 11 de maio de 2015 09:25
  • Ruberlei,

    A nível de informação, eu fiz alguns testes com o SQL Server 2012 SP2 nas edições Express, Standard e Enterprise e não tive problemas.

    Vale ressaltar que todas as edições foram testadas rodando no Windows Server 2008 R2, Windows Server 2012 e Windows Server 2012 R2.


    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 12 de maio de 2015 15:55