Doppelte Schlüsselzeilen aus der Tabelle "sys.syscommittab" in SQL Server

Dieser Artikel enthält Informationen zum Beheben eines SQL Server-Änderungsnachverfolgungsproblems, das zu doppelten Zeilen in sys.syscommittab der Datei führen kann.

Ursprüngliche Produktversion: SQL Server 2008 und die höheren Versionen
Ursprüngliche KB-Nummer: 3083381

Symptome

Wenn Sie den Arbeitsspeicher SYSCOMMITTABLE und die Datei auf dem Datenträger sys.syscommittab in Microsoft SQL Server vergleichen, werden möglicherweise doppelte Schlüsselzeilen angezeigt. Diese doppelten Werte können dazu führen, dass Sicherungs- und Prüfpunktvorgänge fehlschlagen.

Es kann keine doppelte Schlüsselzeile in das Objekt 'sys.syscommittab' mit dem eindeutigen Index 'si_xdes_id' eingefügt werden. Der doppelte Schlüsselwert ist (KeyValue).
Fehler: 3999, Schweregrad: 17, Status: 1.
Fehler beim Leeren der Commit-Tabelle auf den Datenträger in dbidDatabaseID aufgrund des Fehlers 2601. Weitere Informationen finden Sie im Fehlerprotokoll."

Ursache

Dieses Problem tritt aufgrund eines bekannten Problems in der SQL Server-Änderungsnachverfolgung auf.

Beheben von Faktoren, die zu doppelten Schlüsseln führen

Um die Faktoren zu beheben, die die doppelten Schlüssel verursachen, wenden Sie eine der folgenden Fixes an, je nach Ihrer Situation:

• FIX: Ein Sicherungsvorgang in einer SQL Server 2008- oder SQL Server 2008 R2-Datenbank schlägt fehl, wenn Sie die Änderungsnachverfolgung für diese Datenbank aktivieren.

• FIX: Fehler bei der Sicherung in SQL Server 2008, SQL Server 2008 R2 oder SQL Server 2012, wenn Sie die Änderungsnachverfolgung in der Datenbank aktivieren

• FIX: Der Sicherungsvorgang schlägt in einer SQL Server 2008-, SQL Server 2008 R2- oder SQL Server 2012-Datenbank fehl, nachdem Sie die Änderungsnachverfolgung aktiviert haben.

Obwohl diese Korrekturen verhindern, dass doppelte Schlüsselzeilen weiterhin angezeigt werden, werden die doppelten Zeilen nicht automatisch entfernt. Ohne das Entfernen der doppelten Zeilen kann die betroffene Datenbank keine Datenbankprüfpunkte abschließen, und Sicherungen können fehlschlagen.

Deaktivieren und aktivieren Sie die Änderungsnachverfolgung, um doppelte Zeilen zu entfernen.

1. Deaktivieren Sie die Änderungsnachverfolgung für die betroffenen Tabellen und Datenbanken.
2. Stellen Sie einen manuellen Datenbankprüfpunkt aus.
3. Aktivieren Sie die Änderungsnachverfolgung für die betroffene Datenbank und Tabellen.

Weitere Informationen zur Änderungsnachverfolgung finden Sie unter Aktivieren und Deaktivieren der Änderungsnachverfolgung. Informationen zum Ausgeben eines manuellen Prüfpunkts finden Sie unter CHECKPOINT (Transact-SQL).

Manuelles Löschen der doppelten Zeilen

1. Kopieren Sie das Transact-SQL Skript aus dem Abschnitt Transact-SQL Skripts in einen Text-Editor.
2. Suchen Sie den <AFFECTED_DB> Platzhalter im Skript, und ersetzen Sie ihn durch den Namen der betroffenen Datenbank.
3. Speichern Sie das geänderte Skript auf ihrer Festplatte als .sql Datei. Beispiel: C:\temp\remove_duplicates.sql.

Wenn Sie SQL Server 2014 und neuere Versionen verwenden, müssen Sie der Dienst-SID den Vollzugriff auf die mssqlsystemresource.ldf-Dateien und mssqlsystemresource.mdf gewähren. Gehen Sie dazu wie folgt vor:

1. Navigieren Sie zum Bin-Verzeichnis, das Ihrer Instanz-ID entspricht. Beispiel:
   C:\Program Files\Microsoft SQL Server\<Instance ID>\MSSQL\Binn

2. Öffnen Sie die Eigenschaften für mssqlsystemresource.ldf und mssqlsystemresource.mdfund wählen Sie dann die Registerkarte "Sicherheit " aus.

3. Suchen Sie die SQL Server-Dienst-SID pro Dienst, und notieren Sie sich die Standardberechtigungen:

   • *Read & execute
   • *Read

4. Gewähren Sie dem SQL Server-Dienst pro Dienst die Vollzugriffs-SID, und schließen Sie dann die Berechtigungsdialogfelder.

5. Starten Sie SQL Server im Einzelbenutzermodus. Weitere Informationen finden Sie unter Starten von SQL Server im Einzelbenutzermodus.

   Hinweis

   Wenn SQL Server im Einzelbenutzermodus gestartet wird, überspringt er den Startvorgang für Datenbanken, die Teil einer Always On-Verfügbarkeitsgruppe (AG) sind. Wenn Sie ein Problem mit der Änderungsnachverfolgung beheben müssen, bei dem SQL Server im Einzelbenutzermodus gestartet werden muss und die Datenbank mit aktivierter Änderungsnachverfolgung ebenfalls Teil einer AG ist, müssen Sie die Datenbank aus der AG entfernen, bevor Sie SQL Server im Einzelbenutzermodus starten, damit die Datenbank online ist.

6. Verwenden Sie eine sqlcmd Befehlszeile, um eine Verbindung mit SQL Server unter der dedizierten Administratorverbindung (DAC) herzustellen und das geänderte Transact-SQL Skript auszuführen. Beispiel:

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

7. Starten Sie SQL Server im Mehrbenutzermodus neu, und überprüfen Sie dann, ob Sicherungs- und PRÜFPUNKTvorgänge für die betroffene Datenbank erfolgreich abgeschlossen wurden. Wenn Schritt 4 verwendet wurde, stellen Sie die Berechtigungen auf die Standardwerte zurück.

Transact-SQL-Skript

--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