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

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

Ursprüngliche Produktversion: SQL Server 2008 und höher
Ursprüngliche KB-Nummer: 3083381

Problembeschreibung

Wenn Sie den Speicher 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 zu Fehlern bei Sicherungs- und Prüfpunktvorgängen führen.

"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 Committabelle 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 SQL Server Änderungsnachverfolgung auf.

Beheben von Faktoren, die die doppelten Schlüssel verursachen

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

• FEHLERBEHEBUNG: Ein Sicherungsvorgang für eine 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 für die Datenbank aktivieren

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

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

Deaktivieren und Aktivieren der Änderungsnachverfolgung, um doppelte Zeilen zu entfernen

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

Weitere Informationen zur Änderungsnachverfolgung finden Sie unter Aktivieren und Deaktivieren der Änderungsnachverfolgung. Informationen zum Ausstellen 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-Skript 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 höher verwenden, müssen Sie der dienstspezifischen SID die vollständige Kontrolle über die mssqlsystemresource.ldf Dateien und mssqlsystemresource.mdf gewähren. Gehen Sie dazu wie folgt vor:

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

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

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

   • *Read & execute
   • *Read

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

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

   Hinweis

   Wenn SQL Server im Einzelbenutzermodus gestartet wird, wird der Startvorgang für Datenbanken übersprungen, die Teil einer Always On Verfügbarkeitsgruppe 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 Verfügbarkeitsgruppe ist, müssen Sie die Datenbank aus der Verfügbarkeitsgruppe entfernen, bevor Sie SQL Server im Einzelbenutzermodus starten, damit die Datenbank online geschaltet wird.

6. Verwenden Sie eine sqlcmd Befehlszeile, um eine Verbindung mit SQL Server unter der Dedizierten Administratorverbindung (Dedicated Administrator Connection, DAC) herzustellen, und führen Sie das geänderte Transact-SQL-Skript aus. Zum 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 CHECKPOINT-Vorgänge für die betroffene Datenbank erfolgreich abgeschlossen wurden. Wenn Schritt 4 verwendet wurde, rückgängig machen die Berechtigungen auf die Standardwerte.

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