Dupliquer des lignes de clé à partir de la table sys.syscommittab dans SQL Server

Cet article fournit des informations sur la résolution d’un problème de suivi des modifications SQL Server qui peut entraîner des lignes en double dans un sys.syscommittab fichier.

Version du produit d’origine : SQL Server 2008 et les versions ultérieures
Numéro de base de connaissances d’origine : 3083381

Symptômes

Lorsque vous comparez le fichier en mémoire SYSCOMMITTABLE et le fichier sur disque sys.syscommittab dans Microsoft SQL Server, vous pouvez voir des lignes clés dupliquées. Ces valeurs en double peuvent entraîner l’échec des opérations de sauvegarde et de point de contrôle.

« Impossible d’insérer une ligne de clé dupliquée dans l’objet 'sys.syscommittab' avec un index unique 'si_xdes_id'. La valeur de clé dupliquée est (KeyValue).
Erreur : 3999, Gravité : 17, État : 1.
Échec de l'écriture de la table de transaction sur le disque dans dbidDatabaseID en raison de l’erreur 2601. Pour plus d’informations, consultez le journal des erreurs. »

La cause

Ce problème se produit en raison d’un problème connu dans le suivi des modifications SQL Server.

Résoudre les facteurs qui provoquent les clés dupliquées

Pour résoudre les facteurs qui provoquent les clés en double, appliquez l’un des correctifs suivants, le cas échéant :

• CORRECTIF : Une opération de sauvegarde sur une base de données SQL Server 2008 ou SQL Server 2008 R2 échoue si vous activez le suivi des modifications sur cette base de données

• CORRECTIF : La sauvegarde échoue dans SQL Server 2008, SQL Server 2008 R2 ou SQL Server 2012 si vous activez le suivi des modifications sur la base de données

• CORRECTIF : L’opération de sauvegarde échoue dans une base de données SQL Server 2008, SQL Server 2008 R2 ou SQL Server 2012 après avoir activé le suivi des modifications

Bien que ces correctifs empêchent les lignes de clés dupliquées de continuer à apparaître, ils ne suppriment pas automatiquement les lignes dupliquées. Sans supprimer les lignes dupliquées, la base de données affectée ne peut pas terminer les points de contrôle de base de données et les sauvegardes peuvent échouer.

Désactiver et activer le suivi des modifications pour supprimer les lignes dupliquées

1. Désactivez le suivi des modifications sur les tables et la base de données affectées.
2. Effectuez un point de contrôle manuel de la base de données.
3. Activez le suivi des modifications sur la base de données et les tables affectées.

Pour plus d’informations sur le suivi des modifications, consultez Activer et désactiver le suivi des modifications. Pour émettre un point de contrôle manuel, consultez CHECKPOINT (Transact-SQL).

Supprimer manuellement les lignes dupliquées

1. Copiez le scriptTransact-SQL de la section de script Transact-SQL dans un éditeur de texte.
2. Recherchez l’espace <AFFECTED_DB> réservé dans le script et remplacez-le par le nom de la base de données affectée.
3. Enregistrez le script modifié sur votre disque dur sous la forme d’un fichier .sql. Par exemple : C:\temp\remove_duplicates.sql.

Si vous utilisez SQL Server 2014 et les versions ultérieures, vous devez accorder le contrôle total du SID par service aux fichiers mssqlsystemresource.ldf et mssqlsystemresource.mdf. Pour ce faire, procédez comme suit :

1. Accédez au répertoire Bin qui correspond à votre ID d’instance. Par exemple:
   C:\Program Files\Microsoft SQL Server\<Instance ID>\MSSQL\Binn

2. Ouvrez les propriétés pour mssqlsystemresource.ldf et mssqlsystemresource.mdfsélectionnez l’onglet Sécurité .

3. Recherchez le SID par service du service SQL Server et notez les autorisations par défaut :

   • *Read & execute
   • *Read

4. Accordez au service SQL Server le SID par service et le Contrôle total, puis fermez les boîtes de dialogue des autorisations.

5. Démarrez SQL Server en mode mono-utilisateur. Pour plus d’informations, consultez Démarrer SQL Server en mode mono-utilisateur.

   Remarque

   Quand SQL Server démarre en mode mono-utilisateur, il ignore le processus de démarrage des bases de données qui font partie d’un groupe de disponibilité Always On (AG). Si vous devez résoudre un problème lié au suivi des modifications qui nécessite le démarrage de SQL Server en mode mono-utilisateur et que la base de données avec suivi des modifications activée fait également partie d’un groupe de disponibilité, vous devez supprimer la base de données du groupe de disponibilité avant de démarrer SQL Server en mode mono-utilisateur afin que la base de données soit en ligne.

6. Utilisez une sqlcmd ligne de commande pour vous connecter à SQL Server sous la DAC (Dedicated Administrator Connection) et exécuter le script de Transact-SQL modifié. Par exemple:

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

7. Redémarrez SQL Server en mode multi-utilisateur, puis vérifiez que les opérations de sauvegarde et de point de contrôle sur la base de données affectée se terminent correctement. Si l’étape 4 a été utilisée, rétablissez les autorisations aux valeurs par défaut.

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