Sleutelrijen uit de tabel sys.syscommittab dupliceren in SQL Server

Dit artikel bevat informatie over het oplossen van een SQL Server Wijzigingen bijhouden probleem dat kan leiden tot dubbele rijen in sys.syscommittab het bestand.

Oorspronkelijke productversie: SQL Server 2008 en de latere versies
Origineel KB-nummer: 3083381

Symptomen

Wanneer u het bestand in het geheugen SYSCOMMITTABLE en het bestand op de schijf sys.syscommittab vergelijkt in Microsoft SQL Server, ziet u mogelijk dubbele sleutelrijen. Deze dubbele waarden kunnen ertoe leiden dat back-up- en controlepuntbewerkingen mislukken.

"Kan geen dubbele sleutelrij invoegen in object 'sys.syscommittab' met de unieke index 'si_xdes_id'. De waarde van de dubbele sleutel is (KeyValue).
Fout: 3999, Ernst: 17, Status: 1.
Kan de doorvoertabel niet leegmaken naar de schijf in dbidDatabaseID vanwege fout 2601. Raadpleeg het foutenlogboek voor meer informatie.

Oorzaak

Dit probleem treedt op vanwege een bekend probleem in SQL Server wijzigingen bijhouden.

Factoren oplossen die de dubbele sleutels veroorzaken

Als u de factoren wilt oplossen die de dubbele sleutels veroorzaken, past u een van de volgende oplossingen toe, afhankelijk van uw situatie:

• OPLOSSING: Een back-upbewerking op een SQL Server 2008- of SQL Server 2008 R2-database mislukt als u wijzigingen bijhouden inschakelt voor deze database

• OPLOSSING: Back-up mislukt in SQL Server 2008, SQL Server 2008 R2 of SQL Server 2012 als u wijzigingen bijhouden in de database inschakelt

• OPLOSSING: Back-upbewerking mislukt in een SQL Server 2008, SQL Server 2008 R2 of SQL Server 2012-database nadat u wijzigingen bijhouden hebt ingeschakeld

Hoewel deze oplossingen voorkomen dat dubbele sleutelrijen blijven worden weergegeven, worden de dubbele rijen niet automatisch verwijderd. Zonder de dubbele rijen te verwijderen, kan de betrokken database geen databasecontrolepunten voltooien en kunnen back-ups mislukken.

Wijzigingen bijhouden uitschakelen en inschakelen om dubbele rijen te verwijderen

1. Schakel wijzigingen bijhouden uit voor de betrokken tabellen en database.
2. Een handmatig databasecontrolepunt uitgeven.
3. Schakel wijzigingen bijhouden in voor de betrokken database en tabellen.

Zie Wijzigingen bijhouden in- en uitschakelen voor meer informatie over het bijhouden van wijzigingen. Zie CHECKPOINT (Transact-SQL) voor het uitgeven van een handmatig controlepunt.

De dubbele rijen handmatig verwijderen

1. Kopieer het Transact-SQL-script uit de sectie Transact-SQL-script naar een teksteditor.
2. Zoek de <AFFECTED_DB> tijdelijke aanduiding in het script en vervang deze door de naam van de betreffende database.
3. Sla het gewijzigde script op de harde schijf op als een .sql-bestand. Bijvoorbeeld C:\temp\remove_duplicates.sql.

Als u SQL Server 2014 en latere versies gebruikt, moet u de SID per service volledig beheer verlenen aan de mssqlsystemresource.ldf bestanden enmssqlsystemresource.mdf. Ga hiervoor als volgt te werk:

1. Navigeer naar de map Bin die overeenkomt met uw exemplaar-id. Bijvoorbeeld:
   C:\Program Files\Microsoft SQL Server\<Instance ID>\MSSQL\Binn

2. Open de eigenschappen voor mssqlsystemresource.ldf en mssqlsystemresource.mdfen selecteer vervolgens het tabblad Beveiliging .

3. Zoek de SQL Server service per service-SID en noteer de standaardmachtigingen:

   • *Read & execute
   • *Read

4. Verdeel de SQL Server service per service-SID volledig beheer en sluit vervolgens de dialoogvensters voor machtigingen.

5. Start SQL Server in de modus voor één gebruiker. Zie Start SQL Server in de modus voor één gebruiker voor meer informatie.

   Opmerking

   Wanneer SQL Server wordt gestart in de modus voor één gebruiker, wordt het opstartproces overgeslagen voor databases die deel uitmaken van een AlwaysOn-beschikbaarheidsgroep (AG). Als u een probleem met het bijhouden van wijzigingen moet oplossen waarvoor SQL Server in de modus voor één gebruiker moet worden gestart en de database waarvoor wijzigingen bijhouden is ingeschakeld, ook deel uitmaakt van een AG, moet u de database uit de ag verwijderen voordat u SQL Server in de modus voor één gebruiker start, zodat de database online komt.

6. Gebruik een sqlcmd opdrachtregel om verbinding te maken met SQL Server onder de DAC (Dedicated Administrator Connection) en voer het gewijzigde Transact-SQL-script uit. Bijvoorbeeld:

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

7. Start SQL Server opnieuw in de modus Voor meerdere gebruikers en controleer of de back-up- en CHECKPOINT-bewerkingen voor de betrokken database zijn voltooid. Als stap 4 is gebruikt, moet u de machtigingen terugzetten naar de standaardwaarden.

Transact-SQL-script

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