Duplicar linhas de chave da tabela sys.syscommittab no SQL Server

Este artigo fornece informações sobre como resolver um problema de SQL Server Controle de Alterações que pode resultar em linhas duplicadas no sys.syscommittab arquivo.

Versão original do produto: SQL Server 2008 e as versões posteriores
Número de KB original: 3083381

Sintomas

Quando você compara o arquivo na memória SYSCOMMITTABLE e o arquivo em disco sys.syscommittab no Microsoft SQL Server, você pode ver linhas de chave duplicadas. Esses valores duplicados podem fazer com que as operações de backup e ponto de verificação falhem.

"Não é possível inserir uma linha de chave duplicada no objeto 'sys.syscommittab' com índice exclusivo 'si_xdes_id'. O valor da chave duplicada é (KeyValue).
Erro: 3999, Gravidade: 17, Estado: 1.
Falha ao liberar a tabela de confirmação para o disco no dbidDatabaseID devido ao erro 2601. Verifique a caixa de erros para obter mais informações."

Motivo

Esse problema ocorre devido a um problema conhecido no SQL Server controle de alterações.

Resolver fatores que causam as chaves duplicadas

Para resolve os fatores que causam as chaves duplicadas, aplique uma das seguintes correções, conforme apropriado para sua situação:

• CORREÇÃO: uma operação de backup em um banco de dados SQL Server 2008 ou SQL Server 2008 R2 falhará se você habilitar o controle de alterações neste banco de dados

• CORREÇÃO: o backup falhará no SQL Server 2008, SQL Server 2008 R2 ou SQL Server 2012 se você habilitar o controle de alterações no banco de dados

• CORREÇÃO: A operação de backup falha em um banco de dados SQL Server 2008, SQL Server 2008 R2 ou SQL Server banco de dados 2012 depois de habilitar o controle de alterações

Embora essas correções impeçam que as linhas de chave duplicadas continuem a aparecer, elas não removem automaticamente as linhas duplicadas. Sem remover as linhas duplicadas, o banco de dados afetado não pode concluir pontos de verificação de banco de dados e os backups podem falhar.

Desabilitar e habilitar o controle de alterações para remover linhas duplicadas

1. Desabilitar o controle de alterações nas tabelas e no banco de dados afetados.
2. Emita um ponto de verificação de banco de dados manual.
3. Habilite o controle de alterações no banco de dados e nas tabelas afetadas.

Para obter mais informações sobre o controle de alterações, consulte Habilitar e desabilitar o controle de alterações. Para emitir um ponto de verificação manual, consulte CHECKPOINT (Transact-SQL).

Excluir manualmente as linhas duplicadas

1. Copie o script Transact-SQL da seção script Transact-SQL em um editor de texto.
2. Localize o <AFFECTED_DB> espaço reservado no script e substitua-o pelo nome do banco de dados afetado.
3. Salve o script modificado no disco rígido como um arquivo .sql. Por exemplo, C:\temp\remove_duplicates.sql.

Se você estiver usando SQL Server versões 2014 e posteriores, deverá conceder o controle completo por SID por serviço aos mssqlsystemresource.ldf arquivos emssqlsystemresource.mdf. Para fazer isso, siga estas etapas:

1. Navegue até o diretório Bin que corresponde à ID da instância. Por exemplo:
   C:\Program Files\Microsoft SQL Server\<Instance ID>\MSSQL\Binn

2. Abra as propriedades para mssqlsystemresource.ldf e mssqlsystemresource.mdf, e selecione a guia Segurança .

3. Localize o SID do serviço SQL Server por serviço e observe as permissões padrão:

   • *Read & execute
   • *Read

4. Conceda o serviço SQL Server por serviço controle completo SID e feche as caixas de diálogo permissões.

5. Inicie SQL Server no modo de usuário único. Para obter mais informações, consulte Iniciar SQL Server no modo de usuário único.

   Observação

   Quando SQL Server começa no modo de usuário único, ele ignora o processo de inicialização para bancos de dados que fazem parte de um grupo de disponibilidade de Always On (AG). Se você precisar solucionar problemas com o controle de alterações que requer iniciar SQL Server no modo de usuário único, e o banco de dados com o controle de alterações habilitado também faz parte de um AG, você deve remover o banco de dados do AG antes de iniciar SQL Server no modo de usuário único para que o banco de dados fique online.

6. Use uma sqlcmd linha de comando para se conectar a SQL Server na DAC (Conexão de Administrador Dedicado) e executar o script Transact-SQL modificado. Por exemplo:

   sqlcmd -S PRODSERV1\MSSQLSERVER -A -E -i c:\temp\remove_duplicates.sql
   

7. Reinicie SQL Server no modo Multiusuário e verifique se as operações de backup e CHECKPOINT no banco de dados afetado são concluídas com êxito. Se a etapa 4 foi usada, reverter as permissões para os valores padrão.

Script Transact-SQL

--Create a temporary database to store the necessary rows required to remove the duplicate data 
USE master
GO
IF EXISTS(SELECT 1 FROM sys.databases WHERE name = 'dbChangeTrackingMetadata')
BEGIN
  DROP DATABASE dbChangeTrackingMetadata
END
GO
CREATE DATABASE dbChangeTrackingMetadata
GO

--Table to store the contents of the SYSCOMMITTABLE
USE dbChangeTrackingMetadata
GO
CREATE TABLE dbo.t_SYSCOMMITTABLE (
commit_ts BIGINT
,xdes_id BIGINT
,commit_lbn BIGINT
,commit_csn BIGINT
,commit_time DATETIME
)
GO

--Table to store the duplicate rows to be removed from the sys.syscommittab table

CREATE TABLE dbo.t_syscommittab (
commit_ts BIGINT
,xdes_id BIGINT
,commit_lbn BIGINT
,commit_csn BIGINT
,commit_time DATETIME
,dbfragid INT
)
GO

--Enable the usage of OPENROWSET
EXEC sys.sp_setbuildresource 1
GO

--Change <AFFECTED_DB> to the database that contains the duplicate values
USE <AFFECTED DB>
GO
DECLARE @rowcount BIGINT
SET @rowcount = 0

--Copy all rows from the SYSCOMMITTABLE INTo the temporary database
INSERT INTO dbChangeTrackingMetadata.dbo.t_SYSCOMMITTABLE
SELECT commit_ts, xdes_id, commit_lbn, commit_csn, commit_time
FROM OPENROWSET (table SYSCOMMITTABLE, db_id (), 0, 0)

--Save the duplicate values INTo the temporary database
INSERT INTO dbChangeTrackingMetadata.dbo.t_syscommittab
SELECT ondisk_ct.* 
FROM sys.syscommittab as ondisk_ct
JOIN dbChangeTrackingMetadata.dbo.t_SYSCOMMITTABLE as inmem_ct
 ON ondisk_ct.xdes_id = inmem_ct.xdes_id

--Delete the duplicate values
DELETE FROM sys.syscommittab
WHERE xdes_id in ( SELECT xdes_id from dbChangeTrackingMetadata.dbo.t_syscommittab )

SET @rowcount = @@rowcount

IF (@rowcount > 0)
BEGIN
  PRINT ''
  PRINT 'DELETED '+CAST(@rowcount as NVARCHAR(10))+' rows from sys.syscommittab that were also stored in SYSCOMMITTABLE'
PRINT ''
END
ELSE
BEGIN
  PRINT ''
  PRINT 'Failed to DELETE DUP rows from sys.syscommittab'
  PRINT ''
END
EXEC sys.sp_setbuildresource 0
GO