复制 SQL Server 中 sys.syscommittab 表中的键行

本文提供有关解决 SQL Server 更改跟踪问题的信息，该问题可能导致 sys.syscommittab 文件中出现重复行。

原始产品版本： SQL Server 2008 和更高版本
原始 KB 数： 3083381

症状

比较Microsoft SQL Server 中的内存 SYSCOMMITTABLE 中和磁盘 sys.syscommittab 文件时，可能会看到重复的键行。 这些重复值可能会导致备份和检查点操作失败。

“无法在具有唯一索引”si_xdes_id“的对象”sys.syscommittab“中插入重复键行。 重复键值为 （KeyValue）。
错误：3999，严重性：17，状态：1。
由于错误 2601，无法将提交表刷新到 dbidDatabaseID 中的磁盘。 有关详细信息，请查看错误日志。

原因

此问题是由于 SQL Server 更改跟踪中的已知问题而发生的。

解决导致重复键的因素

若要解决导致重复键的因素，请根据情况应用以下修复之一：

尽管这些修复阻止重复键行继续显示，但它们不会自动删除重复的行。 如果不删除重复行，受影响的数据库将无法完成数据库检查点，备份可能会失败。

禁用和启用更改跟踪以删除重复行

  1. 对受影响的表和数据库禁用更改跟踪。
  2. 执行手动数据库检查点。
  3. 对受影响的数据库和表启用更改跟踪。

有关更改跟踪的详细信息，请参阅 “启用和禁用更改跟踪”。 有关发出手动检查点，请参阅 CHECKPOINT (Transact-SQL)。

手动删除重复行

  1. 将 Transact-SQL 脚本 从 Transact-SQL 脚本 部分复制到文本编辑器中。
  2. 在 <AFFECTED_DB> 脚本中找到占位符，并将其替换为受影响的数据库的名称。
  3. 将修改后的脚本另存为.sql文件。 例如，C:\temp\remove_duplicates.sql。

如果使用 SQL Server 2014 及更高版本，则必须向每个服务 SID 授予对 mssqlsystemresource.ldf 文件和 mssqlsystemresource.mdf 文件的完全控制。 为此，请执行以下步骤：

  1. 导航到对应于实例 ID 的 Bin 目录。 例如：
    C:\Program Files\Microsoft SQL Server\<Instance ID>\MSSQL\Binn

  2. 打开属性 mssqlsystemresource.ldf ， mssqlsystemresource.mdf然后选择 “安全 ”选项卡。

  3. 找到 SQL Server 服务的每个服务 SID，并确认默认权限：

    • *Read & execute
    • *Read

  4. 授予 SQL Server 服务的每服务 SID 完全控制权限，然后关闭权限对话框。

  5. 在单用户模式下启动 SQL Server。 有关详细信息，请参阅 在单用户模式下启动 SQL Server。

    注释

    当 SQL Server 以单用户模式启动时，它将跳过属于 AlwaysOn 可用性组（AG）的数据库的启动过程。 如果需要排查需要以单用户模式启动 SQL Server 的更改跟踪问题，并且启用了更改跟踪的数据库也是 AG 的一部分，则必须在单用户模式下启动 SQL Server 之前从 AG 中删除数据库，以便数据库联机。

  6. 使用命令行通过专用管理员连接（DAC）连接到 SQL Server，并执行修改后的 Transact-SQL 脚本。 例如：

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

  7. 在多用户模式下重启 SQL Server，然后验证针对受影响数据库的备份和 CHECKPOINT作是否已成功完成。 如果使用步骤 4，请将权限还原为默认值。

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
  • Last updated on