Filas de clave duplicadas de la tabla sys.syscommittab en SQL Server

Artículo
03/29/2024

En este artículo se proporciona información sobre cómo resolver un problema de SQL Server Change Tracking que puede dar lugar a filas duplicadas en el sys.syscommittab archivo.

Versión original del producto: SQL Server 2008 y las versiones posteriores
Número de KB original: 3083381

Síntomas

Al comparar el archivo en memoria SYSCOMMITTABLE y el archivo en disco sys.syscommittab en Microsoft SQL Server, es posible que vea filas de clave duplicadas. Estos valores duplicados pueden provocar errores en las operaciones de copia de seguridad y punto de control.

"No se puede insertar una fila de clave duplicada en el objeto 'sys.syscommittab' con el índice único 'si_xdes_id'. El valor de clave duplicado es (KeyValue).
Error: 3999, gravedad: 17, estado: 1.
No se pudo vaciar la tabla de confirmación en el disco en dbidDatabaseID debido al error 2601. Compruebe el registro de errores para obtener más información."

Causa

Este problema se produce debido a un problema conocido en SQL Server seguimiento de cambios.

Resolución de factores que provocan las claves duplicadas

Para resolver los factores que provocan las claves duplicadas, aplique una de las siguientes correcciones, según corresponda para su situación:

Aunque estas correcciones impiden que las filas de claves duplicadas sigan apareciendo, no quitan automáticamente las filas duplicadas. Sin quitar las filas duplicadas, la base de datos afectada no puede completar puntos de comprobación de base de datos y es posible que se produzcan errores en las copias de seguridad.

Deshabilitar y habilitar el seguimiento de cambios para quitar filas duplicadas

Deshabilite el seguimiento de cambios en las tablas y la base de datos afectadas.
Emitir un punto de comprobación de base de datos manual.
Habilite el seguimiento de cambios en la base de datos y las tablas afectadas.

Para obtener más información sobre el seguimiento de cambios, vea Habilitar y deshabilitar el seguimiento de cambios. Para emitir un punto de control manual, vea CHECKPOINT (Transact-SQL).

Eliminar manualmente las filas duplicadas

Copie el script de Transact-SQL de la sección script de Transact-SQL en un editor de texto.
Busque el <AFFECTED_DB> marcador de posición en el script y reemplácelo por el nombre de la base de datos afectada.
Guarde el script modificado en el disco duro como un archivo .sql. Por ejemplo, C:\temp\remove_duplicates.sql.

Si usa SQL Server 2014 y versiones posteriores, debe conceder el control total del SID por servicio a los mssqlsystemresource.ldf archivos y mssqlsystemresource.mdf . Para ello, siga estos pasos:

Vaya al directorio Bin que corresponde al identificador de instancia. Por ejemplo:
C:\Program Files\Microsoft SQL Server\<Instance ID>\MSSQL\Binn
Abra las propiedades de mssqlsystemresource.ldf y mssqlsystemresource.mdfy, a continuación, seleccione la pestaña Seguridad .
Busque el SID SQL Server servicio por servicio y tenga en cuenta los permisos predeterminados:
- *Read & execute
- *Read
Conceda el control total de SID de SQL Server servicio por servicio y, a continuación, cierre los cuadros de diálogo de permisos.
Inicie SQL Server en modo de usuario único. Para obtener más información, vea Iniciar SQL Server en modo de usuario único.

Nota:

Cuando SQL Server se inicia en modo de usuario único, omite el proceso de inicio de las bases de datos que forman parte de un grupo de disponibilidad (AG) de Always On. Si necesita solucionar un problema con el seguimiento de cambios que requiere iniciar SQL Server en modo de usuario único y la base de datos con el seguimiento de cambios habilitado también forma parte de un grupo de disponibilidad, debe quitar la base de datos del grupo de disponibilidad antes de iniciar SQL Server en modo de usuario único para que la base de datos se conecte.
Use una sqlcmd línea de comandos para conectarse a SQL Server en la conexión de administrador dedicado (DAC) y ejecutar el script Transact-SQL modificado. Por ejemplo:
```
sqlcmd -S PRODSERV1\MSSQLSERVER -A -E -i c:\temp\remove_duplicates.sql
```
Reinicie SQL Server en modo multiusuario y compruebe que las operaciones de copia de seguridad y CHECKPOINT en la base de datos afectada se completen correctamente. Si se usó el paso 4, revierta los permisos a los valores predeterminados.

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