Pessoal, quem trabalha com SQL Server certamente já passou por isso: você olha uma tabela e percebe que a sequência de IDs pulou alguns números. O ID 1003 vem logo antes do 1010 e não existe nada entre eles. Na hora vem a pergunta: alguém deletou registros?
A resposta é depende. Pode ter sido isso, mas também pode ter sido outra coisa. A coluna IDENTITY gera os valores em sequência, só que ela não garante que essa sequência fique sem buracos. Rollback, insert com falha e restart do serviço podem consumir valores que nunca chegam a ficar na tabela.
Neste post vou mostrar como o IDENTITY se comporta com rollback e com restart, qual é o papel do IDENTITY_CACHE e como investigar se um gap tem cara de cache, de rollback ou de DELETE.
Rollback não devolve o número
Vamos começar pelo caso mais comum. Veja a tabela abaixo:
CREATE TABLE dbo.Teste
(
ID INT IDENTITY(1,1),
Nome VARCHAR(50)
);
INSERT INTO dbo.Teste (Nome)
VALUES ('A'), ('B'), ('C');
Até aqui temos os IDs 1, 2 e 3. Agora vamos abrir uma transação e desfazer o que ela fez:
BEGIN TRAN;
INSERT INTO dbo.Teste (Nome)
VALUES ('Rodrigo'); -- recebe o ID 4
ROLLBACK;
INSERT INTO dbo.Teste (Nome)
VALUES ('Laura');
SELECT * FROM dbo.Teste;
E o resultado é este:
Repare que o ID 4 não aparece. Ele foi entregue para a transação e, quando veio o rollback, o número não voltou para o IDENTITY. O mesmo pode acontecer quando um insert falha depois que o valor já foi gerado, como em uma violação de constraint.
Ponto-chaveID faltando não quer dizer que uma linha gravada foi apagada. O valor pode simplesmente ter sido consumido por uma operação que nunca foi confirmada.
E o IDENTITY_CACHE?
Desde o SQL Server 2012 o engine pode manter em memória uma faixa de valores de IDENTITY já reservados, isso para ganhar desempenho. Se a instância cai ou é reiniciada, os valores que estavam no cache e ainda não tinham sido usados podem se perder. O resultado é um salto na sequência:
45873
45874
45875
<- restart ou falha da instância
46001
46002
Crespi, e dá para desligar isso?
Dá! A partir do SQL Server 2017 esse comportamento pode ser desabilitado por banco de dados:
ALTER DATABASE SCOPED CONFIGURATION
SET IDENTITY_CACHE = OFF;
AtençãoDesligar o IDENTITY_CACHE não acaba com os gaps de rollback, de insert com falha ou de DELETE. A configuração mexe somente no cache de valores do IDENTITY.
E se o negócio exige uma numeração sem nenhum furo? Aí o IDENTITY não é a ferramenta certa. Ele não deve ser tratado como mecanismo fiscal ou documental, e você vai precisar avaliar uma solução própria, com regras de concorrência e de confirmação.
Saltos pequenos também acontecem
Um gap de 5 ou 6 números é perfeitamente possível. Ele pode ter vindo de:
- uma transação que inseriu alguns registros e levou ROLLBACK;
- vários inserts que falharam depois de consumir os valores;
- registros que existiram e depois foram deletados;
- uma mistura de rollback, falhas e exclusões;
- uma queda ou um restart combinado com o uso do cache.
Regra de investigaçãoO tamanho do gap, sozinho, não diz qual foi a causa. Um salto grande perto de um restart aponta para o cache, mas continua sendo só um indício até você cruzar com outras evidências.
Investigando na prática
1. Encontrando os gaps
O primeiro passo é saber exatamente onde estão os intervalos que faltam:
WITH Sequencia AS
(
SELECT
ID,
LEAD(ID) OVER (ORDER BY ID) AS ProximoID
FROM dbo.Teste
)
SELECT
ID AS ID_Anterior,
ProximoID AS ID_Proximo,
ID + 1 AS PrimeiroFaltante,
ProximoID - 1 AS UltimoFaltante,
ProximoID - ID - 1 AS Quantidade
FROM Sequencia
WHERE ProximoID > ID + 1
ORDER BY ID;
Um retorno típico seria este:
ID_Anterior ID_Proximo PrimeiroFaltante UltimoFaltante Quantidade
———– ———- —————- ————– ———-
3 5 4 4 1
1003 1010 1004 1009 6
45875 46001 45876 46000 125
Cada linha é um intervalo que hoje não existe na tabela. Esses valores podem nunca ter sido gravados ou podem ter sido gravados e removidos depois. A coluna Quantidade mostra o tamanho do gap e ajuda a priorizar a investigação, mas não define a causa.
2. Comparando o contador com os dados
SELECT
MAX(ID) AS MaiorID_NaTabela,
IDENT_CURRENT('dbo.Teste') AS ContadorIdentity
FROM dbo.Teste;
DBCC CHECKIDENT ('dbo.Teste', NORESEED);
Exemplo de retorno da consulta:
MaiorID_NaTabela ContadorIdentity
—————- —————-
46002 46005
Já o DBCC CHECKIDENT traz uma mensagem parecida com esta:
Checking identity information: current identity value ‘46005’,
current column value ‘46002’.
Se o contador interno está maior que o maior ID gravado, valores acima desse ID já foram consumidos ou o contador foi avançado. Isso pode vir de rollback, insert com falha, exclusão dos registros mais recentes, cache ou RESEED.
Agora, se o contador está menor que o maior ID, a história é outra: investigue principalmente um RESEED manual para um valor mais baixo. Já o IDENTITY_INSERT explica outro cenário: quando alguém insere um valor explícito maior que o contador, o SQL Server passa a contar a partir dele e o salto aparece na sequência.
Fica a dicaNa investigação use sempre o DBCC CHECKIDENT com NORESEED. Essa opção só consulta os valores, sem redefinir o contador.
3. Verificando quando a instância reiniciou
-- Último start da instância
SELECT sqlserver_start_time
FROM sys.dm_os_sys_info;
-- Error log atual: 0. Anteriores: 1, 2, 3...
EXEC sys.xp_readerrorlog
0,
1,
'SQL Server is starting';
Exemplo de retorno:
Se a tabela tiver uma coluna confiável com a data de criação, compare os registros imediatamente antes e depois do gap:
SELECT ID, DataCadastro
FROM dbo.Teste
WHERE ID IN (45875, 46001);
ID DataCadastro
—– ———————–
45875 2026-10-05 22:58:40.000
46001 2026-10-05 23:04:12.000
Neste exemplo o último registro antes do salto foi criado às 22:58, a instância subiu às 23:02 e o primeiro registro depois do salto apareceu às 23:04. Essa proximidade reforça a hipótese de perda do IDENTITY_CACHE. Mesmo assim, trate como indício e não como prova isolada.
4. Procurando DELETE no transaction log
O transaction log registra as modificações feitas no banco. Enquanto o trecho que interessa ainda estiver no log ativo, dá para procurar as operações de exclusão ligadas à tabela:
SELECT
l.[Transaction ID],
l.[Current LSN],
l.[Operation],
l.[AllocUnitName],
b.[Begin Time],
b.[Transaction Name],
SUSER_SNAME(b.[Transaction SID]) AS LoginName
FROM sys.fn_dblog(NULL, NULL) AS l
LEFT JOIN sys.fn_dblog(NULL, NULL) AS b
ON b.[Transaction ID] = l.[Transaction ID]
AND b.[Operation] = 'LOP_BEGIN_XACT'
WHERE l.[Operation] = 'LOP_DELETE_ROWS'
AND l.[AllocUnitName] LIKE 'dbo.Teste%'
ORDER BY l.[Current LSN];
Exemplo de retorno:
Transaction ID Current LSN Operation AllocUnitName
————– ———————- ————— ——————
0000:0004a1f2 00000031:00000da0:0002 LOP_DELETE_ROWS dbo.Teste.PK_Teste
Begin Time Transaction Name LoginName
———————– —————- ————
2026-10-05 14:21:07.330 DELETE CORP\joao
Esse retorno é um indício forte de que uma transação executou exclusões em uma unidade de alocação da tabela. Só que ele ainda não confirma, sozinho, que os IDs que faltam são exatamente os registros removidos por essa transação. Para isso é preciso aprofundar a análise do log e cruzar o período, os LSNs e as estruturas afetadas.
Alguns cuidados antes de sair rodando essa consulta:
- a sys.fn_dblog é uma função não documentada e normalmente exige permissões elevadas;
- a consulta pode ser pesada em logs grandes, então use de forma controlada;
- uma linha excluída pode gerar registros em estruturas e índices diferentes;
- a quantidade de LOP_DELETE_ROWS não é, automaticamente, a quantidade exata de registros de negócio;
- não achar DELETE no log ativo não prova que não houve exclusão, pois o trecho pode ter sido truncado ou reutilizado.
Juntando as peças
| Evidência encontrada | Interpretação mais provável |
| LOP_DELETE_ROWS associado à tabela no período | Forte indício de DELETE, ainda falta cruzar com os IDs. |
| Gap próximo de um restart, sem DELETE identificado | A perda do IDENTITY_CACHE passa a ser uma hipótese plausível. |
| Gap pequeno, sem DELETE e sem restart próximo | Rollback ou insert com falha entram como hipóteses relevantes. |
| Contador do IDENTITY menor que o maior ID | Investigar RESEED manual. |
| Log truncado e nenhuma auditoria | A causa continua inconclusiva. |
Conclusão
Gaps no IDENTITY são esperados e, na maioria das vezes, não significam perda de dados. Mas quando a dúvida é se alguém deletou registros, olhar apenas para a sequência atual não resolve. O caminho é localizar os gaps, comparar o contador, verificar os restarts e procurar evidências no transaction log e na auditoria que estiver disponível.
Crespi, e se eu não encontrar evidência nenhuma?
Aí a resposta tecnicamente responsável é: inconclusivo. Minha dica para os ambientes onde essa pergunta precisa de uma resposta segura é agir antes. SQL Server Audit, Extended Events, CDC, tabelas temporais ou auditoria na aplicação precisam estar configurados antes de o incidente acontecer.
Próximo passoMantenha uma política de retenção dos SQL Server Error Logs e dos backups de transaction log, além de uma auditoria compatível com o risco do ambiente. É a evidência histórica que transforma uma suspeita em uma conclusão que você consegue defender.
Era isso, Pessoal! Espero que este post seja útil na próxima vez que alguém perguntar quem deletou os registros. Até a próxima!
Abraço, Rodrigo
Referências
- Microsoft Learn. IDENTITY (Property) (Transact-SQL). https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property
- Microsoft Learn. sys.identity_columns (Transact-SQL). https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-identity-columns-transact-sql
- Microsoft Learn. DBCC CHECKIDENT (Transact-SQL). https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql
- Microsoft Learn. SQL Server transaction log architecture and management guide. https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide
- Microsoft Learn. ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL). https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql