Righe di chiave duplicate dalla tabella sys.syscommittab in SQL Server

Questo articolo fornisce informazioni sulla risoluzione di un problema di SQL Server Rilevamento modifiche che può causare righe duplicate nel sys.syscommittab file.

Versione originale del prodotto: SQL Server 2008 e versioni successive
Numero KB originale: 3083381

Sintomi

Quando si confrontano il file in memoria SYSCOMMITTABLE e il file su disco sys.syscommittab in Microsoft SQL Server, è possibile che vengano visualizzate righe di chiave duplicate. Questi valori duplicati possono causare l'esito negativo delle operazioni di backup e checkpoint.

"Impossibile inserire una riga di chiave duplicata nell'oggetto 'sys.syscommittab' con indice univoco 'si_xdes_id'. Il valore della chiave duplicato è (KeyValue).
Errore: 3999, Gravità: 17, Stato: 1.
Impossibile scaricare la tabella di commit su disco in dbidDatabaseID a causa dell'errore 2601. Controllare il log degli errori per altre informazioni."

Causa

Questo problema si verifica a causa di un problema noto nel rilevamento delle modifiche SQL Server.

Risolvere i fattori che causano le chiavi duplicate

Per risolvere i fattori che causano le chiavi duplicate, applicare una delle correzioni seguenti, in base alla situazione:

• FIX: un'operazione di backup in un database SQL Server 2008 o SQL Server 2008 R2 non riesce se si abilita il rilevamento delle modifiche nel database

• CORREZIONE: il backup non riesce in SQL Server 2008, SQL Server 2008 R2 o SQL Server 2012 se si abilita il rilevamento delle modifiche nel database

• FIX: l'operazione di backup non riesce in un database SQL Server 2008, SQL Server 2008 R2 o SQL Server 2012 dopo l'abilitazione del rilevamento delle modifiche

Anche se queste correzioni impediscono che le righe chiave duplicate continuino a essere visualizzate, non rimuovono automaticamente le righe duplicate. Senza rimuovere le righe duplicate, il database interessato non può completare i checkpoint del database e i backup potrebbero non riuscire.

Disabilitare e abilitare il rilevamento delle modifiche per rimuovere le righe duplicate

1. Disabilitare il rilevamento delle modifiche nelle tabelle e nel database interessati.
2. Eseguire un checkpoint del database manuale.
3. Abilitare il rilevamento delle modifiche nel database e nelle tabelle interessate.

Per altre informazioni sul rilevamento delle modifiche, vedere Abilitare e disabilitare il rilevamento delle modifiche. Per l'emissione di un checkpoint manuale, vedere CHECKPOINT (Transact-SQL).For issuing a manual Checkpoint, see CHECKPOINT (Transact-SQL).

Eliminare manualmente le righe duplicate

1. Copiare lo script Transact-SQL dalla sezione script Transact-SQL in un editor di testo.
2. Individuare il <AFFECTED_DB> segnaposto nello script e sostituirlo con il nome del database interessato.
3. Salvare lo script modificato nel disco rigido come file .sql. Ad esempio, C:\temp\remove_duplicates.sql.

Se si usa SQL Server 2014 e versioni successive, è necessario concedere il controllo completo del SID per servizio ai mssqlsystemresource.ldf file e mssqlsystemresource.mdf . A tal fine, attenersi alla seguente procedura:

1. Passare alla directory Bin corrispondente all'ID istanza. Ad esempio:
   C:\Program Files\Microsoft SQL Server\<Instance ID>\MSSQL\Binn

2. Aprire le proprietà per mssqlsystemresource.ldf e mssqlsystemresource.mdfe quindi selezionare la scheda Sicurezza .

3. Individuare il SID del servizio SQL Server per servizio e prendere nota delle autorizzazioni predefinite:

   • *Read & execute
   • *Read

4. Concedere al servizio SQL Server controllo completo SID per servizio e quindi chiudere le finestre di dialogo delle autorizzazioni.

5. Avviare SQL Server in modalità utente singolo. Per altre informazioni, vedere Avviare SQL Server in modalità utente singolo.

   Nota

   Quando SQL Server viene avviato in modalità utente singolo, ignora il processo di avvio per i database che fanno parte di un gruppo di disponibilità Always On.When SQL Server starts in single-user mode, it skips the startup process for databases that are part of an Always On availability group (AG). Se è necessario risolvere un problema con il rilevamento delle modifiche che richiede l'avvio SQL Server in modalità utente singolo e anche il database con rilevamento delle modifiche abilitato fa parte di un gruppo di disponibilità, è necessario rimuovere il database dal gruppo di disponibilità prima di avviare SQL Server in modalità utente singolo in modo che il database venga online.

6. Usare una sqlcmd riga di comando per connettersi a SQL Server in Connessione amministratore dedicato (DAC) ed eseguire lo script Transact-SQL modificato. Ad esempio:

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

7. Riavviare SQL Server in modalità multiutente e quindi verificare che le operazioni di backup e CHECKPOINT sul database interessato siano state completate correttamente. Se è stato usato il passaggio 4, ripristinare le autorizzazioni ai valori predefiniti.

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