Zduplikowane wiersze kluczy z tabeli sys.syscommittab w SQL Server

Ten artykuł zawiera informacje dotyczące rozwiązywania problemu SQL Server Change Tracking, który może spowodować zduplikowanie wierszy w sys.syscommittab pliku.

Oryginalna wersja produktu: SQL Server 2008 r. i nowsze wersje
Oryginalny numer KB: 3083381

Symptomy

Podczas porównywania pliku w pamięci SYSCOMMITTABLE i pliku na dysku sys.syscommittab w usłudze Microsoft SQL Server mogą zostać wyświetlone zduplikowane wiersze kluczy. Te zduplikowane wartości mogą powodować niepowodzenie operacji tworzenia kopii zapasowych i punktów kontrolnych.

"Nie można wstawić zduplikowanego wiersza klucza w obiekcie 'sys.syscommittab' z unikatowym indeksem "si_xdes_id". Zduplikowana wartość klucza to (KeyValue).
Błąd: 3999, ważność: 17, stan: 1.
Nie można opróżnić tabeli zatwierdzeń na dysku w dbidDatabaseID z powodu błędu 2601. Sprawdź dziennik błędów, aby uzyskać więcej informacji."

Przyczyna

Ten problem występuje z powodu znanego problemu w SQL Server śledzenia zmian.

Rozwiązywanie czynników powodujących zduplikowane klucze

Aby rozwiązać czynniki powodujące zduplikowane klucze, zastosuj jedną z następujących poprawek, stosownie do sytuacji:

• POPRAWKA: Operacja tworzenia kopii zapasowej bazy danych SQL Server 2008 lub SQL Server 2008 R2 kończy się niepowodzeniem, jeśli włączysz śledzenie zmian w tej bazie danych

• POPRAWKA: Tworzenie kopii zapasowej kończy się niepowodzeniem w SQL Server 2008 r., SQL Server 2008 R2 lub SQL Server 2012 r., jeśli włączysz śledzenie zmian w bazie danych

• POPRAWKA: Operacja tworzenia kopii zapasowej kończy się niepowodzeniem w bazie danych SQL Server 2008, SQL Server 2008 R2 lub SQL Server 2012 po włączeniu śledzenia zmian

Mimo że te poprawki uniemożliwiają dalsze wyświetlanie zduplikowanych wierszy kluczy, nie usuwają automatycznie zduplikowanych wierszy. Bez usuwania zduplikowanych wierszy baza danych, których dotyczy problem, nie może ukończyć punktów kontrolnych bazy danych, a tworzenie kopii zapasowych może zakończyć się niepowodzeniem.

Wyłączanie i włączanie śledzenia zmian w celu usunięcia zduplikowanych wierszy

1. Wyłącz śledzenie zmian w tabelach i bazie danych, których dotyczy problem.
2. Wystawianie ręcznego punktu kontrolnego bazy danych.
3. Włącz śledzenie zmian w bazie danych i tabelach, których dotyczy problem.

Aby uzyskać więcej informacji na temat śledzenia zmian, zobacz Włączanie i wyłączanie śledzenia zmian. Aby uzyskać ręczny punkt kontrolny, zobacz CHECKPOINT (Transact-SQL).

Ręczne usuwanie zduplikowanych wierszy

1. Skopiuj skrypt Języka Transact-SQL z sekcji Transact-SQL script do edytora tekstów.
2. Znajdź symbol zastępczy <AFFECTED_DB> w skrypcie i zastąp go nazwą bazy danych, których dotyczy problem.
3. Zapisz zmodyfikowany skrypt na dysku twardym jako plik .sql. Na przykład C:\temp\remove_duplicates.sql.

Jeśli używasz SQL Server wersji 2014 lub nowszej, musisz przyznać identyfikatorowi SID usługi pełną mssqlsystemresource.ldf kontrolę nad plikami imssqlsystemresource.mdf. Aby to zrobić, wykonaj następujące kroki.

1. Przejdź do katalogu Bin odpowiadającego identyfikatorowi wystąpienia. Przykład:
   C:\Program Files\Microsoft SQL Server\<Instance ID>\MSSQL\Binn

2. Otwórz właściwości dla mssqlsystemresource.ldf i mssqlsystemresource.mdf, a następnie wybierz kartę Zabezpieczenia .

3. Znajdź identyfikator SID usługi SQL Server dla usługi i zanotuj uprawnienia domyślne:

   • *Read & execute
   • *Read

4. Przyznaj usłudze SQL Server pełną kontrolę nad identyfikatorem SID usługi, a następnie zamknij okna dialogowe uprawnień.

5. Uruchom SQL Server w trybie pojedynczego użytkownika. Aby uzyskać więcej informacji, zobacz Uruchamianie SQL Server w trybie pojedynczego użytkownika.

   Uwaga

   Gdy SQL Server rozpoczyna się w trybie pojedynczego użytkownika, pomija proces uruchamiania baz danych, które są częścią zawsze włączonej grupy dostępności. Jeśli musisz rozwiązać problem ze śledzeniem zmian, który wymaga uruchomienia SQL Server w trybie pojedynczego użytkownika, a baza danych z włączonym śledzeniem zmian jest również częścią grupy dostępności, musisz usunąć bazę danych z grupy dostępności przed rozpoczęciem SQL Server w trybie pojedynczego użytkownika, aby baza danych była w trybie online.

6. Użyj wiersza sqlcmd polecenia, aby nawiązać połączenie z SQL Server w ramach dedykowanego połączenia administratora (DAC) i wykonać zmodyfikowany skrypt Transact-SQL. Przykład:

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

7. Uruchom ponownie SQL Server w trybie wielu użytkowników, a następnie sprawdź, czy operacje tworzenia kopii zapasowych i punktów kontrolnych względem bazy danych, których dotyczy problem, zostały zakończone pomyślnie. Jeśli użyto kroku 4, przywróć uprawnienia do wartości domyślnych.

Skrypt języka 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