简介SQL Server开启CDC后若代理作业异常或出现大事务写入很容易触发“数据库事务日志已满原因为REPLICATION”的报错导致数据操作无法继续。这份PDF围绕该故障展开先用通俗方式说明CDC与复制的日志使用步骤再通过一个512MB日志上限的测试库实际演示SQL Server Agent被关闭后逐条插入数据直至日志写满、报错出现、收缩无效的过程并对比代理开启后作业仍失败的原因。针对已堵塞的日志资源给出了执行sp_repldone标记已分发、待日志截断后恢复写入的临时方案同时提及由短时大批量事务引发的类似故障场景。适合DBA、运维人员以及希望深入理解事务日志机制的SQL Server学习者。资源包为单个PDF文件约314KB内容紧凑目前已有1782人学习可见该问题颇具代表性下载后可作为日志异常排查的参考手册。1. 开着 CDC 的表写入时报 log full due to REPLICATION问题出在日志状态没被释放SQL Server 开启 CDC变更数据捕获后对基础表执行 Insert/Update/Delete 本身不会立刻把日志空间吃掉真正让日志文件被写满的是日志里那些被标记为 Replication 状态、又迟迟没有被取走解析的日志记录。最典型的报错是The transaction log for database TestDB is full due to REPLICATION。这个报错很容易被误判成“日志文件太小”或者“恢复模式不对”实际上问题出在 SQL Server Agent 的捕获作业没有及时消费日志导致日志重用机制失效。很多 DBA 遇到这个报错后第一反应是收缩日志或切简单恢复模式结果发现都不生效原因就在日志内部存在大量“待复制”状态的活动日志。这篇文章会用两个场景复现这个问题给出可执行的排查和处置脚本并解释为什么不能简单依赖收缩日志或重启服务来解决。2. CDC 的日志标记机制为什么日志空间被占用后简单恢复模式也救不了2.1 被标记的日志记录在开启 CDC 的表上执行 DML 操作时事务日志写入的粒度其实和普通表没有本质区别但在日志记录内部CDC 相关的日志记录被标记为“等待捕获”即 Replication 状态。这是 SQL Server 为复制和 CDC 预留的日志管理机制和数据库的恢复模式没有关系即便你把数据库切到简单恢复模式这条日志只要还处于 Replication 状态checkpoint 也不能把它标记为可重用。在 SQL Server 内部每个虚拟日志文件VLF都有一个状态标记位可以通过DBCC LOGINFO查看到。常见状态值包括状态值含义说明0Nothing可重用状态日志空间可以被覆盖1Checkpoint检查点截断边界2Replication等待复制代理或 CDC 捕获作业读取4Active活动事务持有8EOS虚拟日志文件末尾16Precreate预创建状态32Inactive非活动状态64Targeted Replication定向复制标记状态当 CDC 系统表cdc.dbo_test_cdc_CT写入完成之后这些日志记录会被置为可重用。整个链路是日志写入 → 捕获作业读日志 → 解析并写入变更表 → 标记日志可重用 → checkpoint 截断空闲 VLF。任何一环中断都会让占用比例不断上涨。2.2 查看日志等待类型和 VLF 状态当 CDC 开启后日志出现积压第一件事是确认等待类型。通过sys.databases的log_reuse_wait_desc字段能够最快定位问题类型。SELECT name AS database_name, log_reuse_wait_desc, recovery_model_desc, log_size_mb CAST(CAST(size / 128.0 AS DECIMAL(12, 2)) AS DECIMAL(12, 2)) FROM sys.databases WHERE name TestLogFull;log_reuse_wait_desc字段如果返回REPLICATION说明日志尾部存在被标记为 Replication 状态且未被捕获作业消费的日志记录。此时无论手动执行CHECKPOINT还是DBCC SHRINKFILE都无法释放这部分空间因为在 SQL Server 的日志管理逻辑里这类日志属于“不属于当前活动事务、但又不能重用”的中间状态。接着用DBCC LOGINFO查看具体 VLF 分布DBCC LOGINFO(TestLogFull);输出里Status列为 2 的 VLF 数量如果占大多数说明这些虚拟日志文件都被 Replication 状态占用。RecoveryUnitId和FileId用于区分不同日志文件下的 VLF在多日志文件场景下需要结合这两列判断是哪个物理日志文件积压。2.3 日志截断链路简单恢复模式下日志截断发生在 checkpoint 之后但 checkpoint 并不能截断 Replication 状态的日志。日志重用条件有三个该日志不是活动事务持有的日志、不处于 Replication 或 Targeted Replication 状态、不在备份或还原链路的保留区间内。所以当复制或 CDC 作业无法消费日志时日志空间就变成了一块“死空间”新事务无法覆盖这部分日志。提示开启 CDC 的数据库日志增长的风格会从“事务性波动”变成“持续递增”因为每次 DML 都会产生一条被标记的日志记录直到捕获作业完成消费后才释放。3. 完整复现SQL Server Agent 未启动导致的日志积压与处理3.1 搭建测试环境和启用 CDC先创建一个限制日志最大大小的数据库。日志文件初始大小设小一点是为了让问题更快暴露出来生产环境如果日志文件被设了上限或者磁盘本身不足现象是一样的。USE master; GO CREATE DATABASE TestLogFull ON PRIMARY ( NAME NTestLogFull, FILENAME ND:\DBFile\TestLogFull\TestLogFull.mdf, SIZE 500MB, MAXSIZE UNLIMITED, FILEGROWTH 100MB ) LOG ON ( NAME NTestLogFull_log, FILENAME ND:\DBFile\TestLogFull\TestLogFull_Log.ldf, SIZE 1MB, MAXSIZE 512MB, FILEGROWTH 100MB ); GO这里把日志文件最大大小设置为 512MBFILEGROWTH设为 100MB意思是每次日志空间不足自动增长 100MB但总大小不能超过 512MB。如果不设置MAXSIZE在磁盘空间不设限的情况下日志会一直增长到把磁盘写满并不会触发 “log full” 报错所以复现这个现象依赖日志大小限制。接着启用数据库级别的 CDC并创建一张测试表USE TestLogFull; GO EXECUTE sys.sp_cdc_enable_db; GO CREATE TABLE dbo.test_cdc ( id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(50), mail VARCHAR(50), address NVARCHAR(50), lastupdatetime DATETIME ); GO EXEC sys.sp_cdc_enable_table source_schema dbo, source_name test_cdc, role_name cdc_admin, capture_instance DEFAULT, supports_net_changes 1, index_name NULL, filegroup_name DEFAULT; GOsp_cdc_enable_table里的supports_net_changes设为 1 时SQL Server 会额外维护一个dbo_test_cdc_CT表的合并更新机制用来支持cdc.fn_cdc_get_net_changes_dbo_test_cdc这类查询接口同时也意味着捕获作业需要处理两处元数据日志消费路径会比只记录净变更时稍长。capture_instance保持 DEFAULT 时捕获实例的命名规则是架构名_表名也就是dbo_test_cdc。CDC 开启成功后会在系统表中生成对应的捕获实例信息同时创建cdc.dbo_test_cdc_CT变更表。3.2 关闭 Agent 制造日志积压现象SQL Server Agent 是执行捕获作业的宿主进程。关闭 Agent 后日志写入照常发生但cdc.dbo_test_cdc_capture作业无法运行Replication 状态的日志无法被消费。用一个循环插入脚本模拟持续写入USE TestLogFull; GO SET NOCOUNT ON; DECLARE i INT 1; DECLARE batch INT 5000; WHILE i 100000 BEGIN INSERT INTO dbo.test_cdc (name, mail, address, lastupdatetime) VALUES (CONCAT(user_, i), CONCAT(user_, i, example.com), CONCAT(address_, i), GETDATE()); SET i i 1; IF (i % batch 0) BEGIN CHECKPOINT; END END GOCHECKPOINT的作用是触发 checkpoint 截断逻辑。在简单恢复模式下checkpoint 会刷新脏页并尝试截断可重用的日志空间如果部分日志被标记为 Replication则这部分仍然保留而其他非 Replication 部分可以正常重用。在 Agent 服务关闭的场景下日志会持续积压在 “状态为 2” 的 VLF 中直到日志文件达到设定的最大大小 512MB此时所有写入操作都会被阻塞报错信息就会是 “The transaction log for database is full due to ‘REPLICATION’”。此时执行查询确认日志等待状态SELECT log_reuse_wait_desc FROM sys.databases WHERE name TestLogFull;返回结果是REPLICATION就可以确认是捕获作业没有消费日志导致的。执行DBCC SHRINKFILE也不会有效果因为日志尾部空间被活动日志占满收缩操作本身就需要日志尾部可重用空间的支持。3.3 用 sp_repldone 应急释放日志空间启动 SQL Server Agent 服务后你会发现捕获作业依然无法执行原因很直接捕获作业本身也需要写日志而此时日志文件已经没有可用空间。这种情况下只能手动将待复制的日志标记为已分发。USE TestLogFull; GO EXEC sys.sp_repldone xactid NULL, xact_seqno NULL, numtrans 0, time 0, reset 1; GOreset 1表示将所有已复制但尚未标记完成的事务日志全部标记为已分发xactid和xact_seqno设置为 NULL 时配合reset 1使用表示不针对某个具体事务而是重置整条日志分发标记。numtrans 0表示不限定具体事务数量。执行完成后日志空间的释放不会立即发生。此时执行一条写入语句比如插入一行无关紧要的数据触发 checkpoint 后日志空间就会被截断释放。验证方法DBCC LOGINFO(TestLogFull);执行后观察输出的Status列如果大量 VLF 的 Status 已经是 0说明空间已恢复为可重用状态。提示这个操作是应急方案不是常规维护手段。手动标记为已分发的日志对应的 CDC 变更记录并不会补写到变更表CDC 数据链路会出现断裂下游拿不到这部分变化数据。4. 短时间大批量写入导致日志积压的第二个场景4.1 大事务让日志写入速度超过捕获消费速度第二个场景和 Agent 无关。即便 SQL Server Agent 正常运行捕获作业也会因为调度频率、日志读取速度等原因在高吞吐写入时出现消费速度跟不上产生速度的情况。捕获作业读取日志的机制是轮询默认每 5 秒扫描一次日志如果写入量远大于 5 秒内能处理完的量Replication 状态的日志就会积累。日常生产环境最常遇到的问题其实就是这类问题典型场景是数据迁移、批量导入、初始化同步等。当日志文件大小被限制时只要写入速度持续高于捕获作业的消费速度日志文件就会在某个时间点被写满。后续表现和第一个场景一样Agent 作业会因为日志空间不足而失败进而是死循环日志满 → 作业失败 → 日志无法释放 → 后续写入全部阻塞。应对这个场景添加日志文件或扩大日志文件上限是直接有效的办法核心是让捕获作业先跑起来把日志消费到安全水位后再做收缩或调整。USE master; GO ALTER DATABASE TestLogFull ADD LOG FILE ( NAME NTestLogFull_log2, FILENAME ND:\DBFile\TestLogFull\TestLogFull_Log2.ldf, SIZE 1024MB, MAXSIZE UNLIMITED, FILEGROWTH 256MB ); GO执行ADD LOG FILE后日志文件空间立即生效不需要重启服务。这里初始大小给 1024MB是为了确保在捕获作业积压量比较大的情况下作业有足够空间完成日志解析和写入操作。MAXSIZE建议在生产环境保持UNLIMITED如果无法做到不限大小至少要保证磁盘剩余空间大于日志文件当前大小的 1.5 倍给缓冲留出余地。执行完添加日志文件的语句之后需要等待一段时间让捕获作业继续工作。日志解析是逐步进行的积压的日志量越大作业运行时间越长。观察以下查询的输出变化SELECT instance_name capture_instance, start_time start_time, last_commit_time last_commit_time, row_count row_count FROM cdc.dbo_test_cdc_CT;如果row_count持续增加说明捕获作业正在消费日志。注意不要在这期间盲目执行SHRINKFILE因为日志文件仍然包含尚未完全释放的 VLF收缩动作会打断捕获作业的处理节奏。4.2 日志文件增长被磁盘空间限制时的处理顺序如果日志满的原因不是文件大小上限而是磁盘剩余空间不足先观察磁盘空间EXEC sys.xp_readerrorlog 0, 1, Ntransaction log, Nfull;这条命令会读取 SQL Server 错误日志中包含关键字 “transaction log” 或 “full” 的记录能看到日志文件是否触发了自动增长失败。如果磁盘确实没有剩余空间先压缩或迁移其他非必要文件腾出空间再执行上面的ADD LOG FILE。DBCC SQLPERF(LOGSPACE)可以快速查看所有数据库的日志文件使用率DBCC SQLPERF(LOGSPACE);返回结果中的Log Space Used (%)和Log Size (MB)用于判断哪些数据库的日志已经接近上限。如果某库的日志使用率长期高于 90%并且log_reuse_wait_desc是REPLICATION就需要优先排查 CDC 作业状态。4.3 重启服务的边际效果说明在 SQL Server 2014 SP2 及以上版本中如果存放日志的磁盘有可用空间重启 SQL Server 服务后日志文件会自动扩展一小段空间物理大小可能超过MAXSIZE设置。这个现象是服务重启过程中日志文件初始化逻辑导致的不能依赖它作为解决方案。问题在于如果 Agent 没有设置为随服务自动启动重启服务后日志积压问题会重复出现。正确做法是在服务配置里确认 SQL Server Agent 的启动模式是“自动”同时确保 CDC 捕获作业处于启用状态。5. 巡检脚本和积压判断技巧提前发现 REPLICATION 状态日志堆积5.1 快速定位风险库生产环境不建议等日志满后再处理维护窗口内跑一遍巡检脚本可以在日志占比达到 70% 到 80% 时提前发现风险。SELECT d.name AS dbname, d.log_reuse_wait_desc, d.recovery_model_desc, usg.total_log_size_mb, usg.used_log_size_mb, usg.used_log_percent, d.is_cdc_enabled FROM sys.databases d CROSS APPLY sys.dm_db_log_space_usage(d.database_id) usg WHERE d.is_cdc_enabled 1 ORDER BY usg.used_log_percent DESC;sys.dm_db_log_space_usage返回数据库日志文件的总大小、已使用大小和使用百分比比DBCC SQLPERF(LOGSPACE)更精确且支持CROSS APPLY过滤。is_cdc_enabled过滤出所有开启 CDC 的库避免在非 CDC 库上做无效排查。used_log_percent超过 80 且log_reuse_wait_desc为REPLICATION时需要立即处理。如果log_reuse_wait_desc是NOTHING说明日志空间处于完全可重用状态此时即便used_log_percent偏高也只是物理文件大小问题不在本文讨论范围内。5.2 检查捕获作业本身的状态CDC 作业卡死后捕获作业的历史记录里通常会有错误信息。可以通过以下方式检查作业当前状态和最近运行时间SELECT j.name AS job_name, ja.start_execution_date, ja.last_executed_step_id, ja.stop_execution_date, ja.last_executed_step_date, CASE WHEN ja.job_id IS NULL THEN not running WHEN ja.stop_execution_date IS NULL THEN running ELSE not running END AS job_status FROM msdb.dbo.sysjobs j LEFT JOIN msdb.dbo.sysjobactivity ja ON ja.job_id j.job_id WHERE j.name LIKE Ncdc.%;sysjobactivity里的start_execution_date和stop_execution_date用于判断作业是否正在运行。如果某 CDC 作业一直处于 running 状态且长达数十分钟未结束说明它在等待日志空间或者被阻塞。关闭 Agent 再启动后作业大概需要 1 到 2 分钟才能重新调度不要立即判断作业失效。5.3 关联 CDC 变更表的数据增长确认流程当日志积压时CDC 变更表的数据量不会增长这两者之间存在强关联。用这条查询确认积压是否已被消费SELECT capture_instance, COUNT(*) AS captured_row_count FROM cdc.dbo_test_cdc_CT GROUP BY capture_instance;如果写入基表的数据量明显增加但captured_row_count没有变化说明捕获作业积压仍未释放。正常状态下基表写入后几秒内变更表就应该出现对应数据。在批量写入场景下这个延迟会被放大但延迟一般不会超过几分钟长时间不增长就说明从日志读取到变更表写入的链路中断了。一条完整的应急处置顺序是先确认log_reuse_wait_desc是REPLICATION然后确认 CDC 作业是否在空转或处于失败状态接着确认磁盘剩余空间按顺序执行启动无效则添加日志文件日志文件不足则用sp_repldone应急释放空间最后等 CDC 作业恢复运行后再重新评估 VLF 分布和日志文件大小设置。本文还有配套的精品资源点击获取
SQL Server CDC日志满问题：REPLICATION状态积压的排查与处置
简介SQL Server开启CDC后若代理作业异常或出现大事务写入很容易触发“数据库事务日志已满原因为REPLICATION”的报错导致数据操作无法继续。这份PDF围绕该故障展开先用通俗方式说明CDC与复制的日志使用步骤再通过一个512MB日志上限的测试库实际演示SQL Server Agent被关闭后逐条插入数据直至日志写满、报错出现、收缩无效的过程并对比代理开启后作业仍失败的原因。针对已堵塞的日志资源给出了执行sp_repldone标记已分发、待日志截断后恢复写入的临时方案同时提及由短时大批量事务引发的类似故障场景。适合DBA、运维人员以及希望深入理解事务日志机制的SQL Server学习者。资源包为单个PDF文件约314KB内容紧凑目前已有1782人学习可见该问题颇具代表性下载后可作为日志异常排查的参考手册。1. 开着 CDC 的表写入时报 log full due to REPLICATION问题出在日志状态没被释放SQL Server 开启 CDC变更数据捕获后对基础表执行 Insert/Update/Delete 本身不会立刻把日志空间吃掉真正让日志文件被写满的是日志里那些被标记为 Replication 状态、又迟迟没有被取走解析的日志记录。最典型的报错是The transaction log for database TestDB is full due to REPLICATION。这个报错很容易被误判成“日志文件太小”或者“恢复模式不对”实际上问题出在 SQL Server Agent 的捕获作业没有及时消费日志导致日志重用机制失效。很多 DBA 遇到这个报错后第一反应是收缩日志或切简单恢复模式结果发现都不生效原因就在日志内部存在大量“待复制”状态的活动日志。这篇文章会用两个场景复现这个问题给出可执行的排查和处置脚本并解释为什么不能简单依赖收缩日志或重启服务来解决。2. CDC 的日志标记机制为什么日志空间被占用后简单恢复模式也救不了2.1 被标记的日志记录在开启 CDC 的表上执行 DML 操作时事务日志写入的粒度其实和普通表没有本质区别但在日志记录内部CDC 相关的日志记录被标记为“等待捕获”即 Replication 状态。这是 SQL Server 为复制和 CDC 预留的日志管理机制和数据库的恢复模式没有关系即便你把数据库切到简单恢复模式这条日志只要还处于 Replication 状态checkpoint 也不能把它标记为可重用。在 SQL Server 内部每个虚拟日志文件VLF都有一个状态标记位可以通过DBCC LOGINFO查看到。常见状态值包括状态值含义说明0Nothing可重用状态日志空间可以被覆盖1Checkpoint检查点截断边界2Replication等待复制代理或 CDC 捕获作业读取4Active活动事务持有8EOS虚拟日志文件末尾16Precreate预创建状态32Inactive非活动状态64Targeted Replication定向复制标记状态当 CDC 系统表cdc.dbo_test_cdc_CT写入完成之后这些日志记录会被置为可重用。整个链路是日志写入 → 捕获作业读日志 → 解析并写入变更表 → 标记日志可重用 → checkpoint 截断空闲 VLF。任何一环中断都会让占用比例不断上涨。2.2 查看日志等待类型和 VLF 状态当 CDC 开启后日志出现积压第一件事是确认等待类型。通过sys.databases的log_reuse_wait_desc字段能够最快定位问题类型。SELECT name AS database_name, log_reuse_wait_desc, recovery_model_desc, log_size_mb CAST(CAST(size / 128.0 AS DECIMAL(12, 2)) AS DECIMAL(12, 2)) FROM sys.databases WHERE name TestLogFull;log_reuse_wait_desc字段如果返回REPLICATION说明日志尾部存在被标记为 Replication 状态且未被捕获作业消费的日志记录。此时无论手动执行CHECKPOINT还是DBCC SHRINKFILE都无法释放这部分空间因为在 SQL Server 的日志管理逻辑里这类日志属于“不属于当前活动事务、但又不能重用”的中间状态。接着用DBCC LOGINFO查看具体 VLF 分布DBCC LOGINFO(TestLogFull);输出里Status列为 2 的 VLF 数量如果占大多数说明这些虚拟日志文件都被 Replication 状态占用。RecoveryUnitId和FileId用于区分不同日志文件下的 VLF在多日志文件场景下需要结合这两列判断是哪个物理日志文件积压。2.3 日志截断链路简单恢复模式下日志截断发生在 checkpoint 之后但 checkpoint 并不能截断 Replication 状态的日志。日志重用条件有三个该日志不是活动事务持有的日志、不处于 Replication 或 Targeted Replication 状态、不在备份或还原链路的保留区间内。所以当复制或 CDC 作业无法消费日志时日志空间就变成了一块“死空间”新事务无法覆盖这部分日志。提示开启 CDC 的数据库日志增长的风格会从“事务性波动”变成“持续递增”因为每次 DML 都会产生一条被标记的日志记录直到捕获作业完成消费后才释放。3. 完整复现SQL Server Agent 未启动导致的日志积压与处理3.1 搭建测试环境和启用 CDC先创建一个限制日志最大大小的数据库。日志文件初始大小设小一点是为了让问题更快暴露出来生产环境如果日志文件被设了上限或者磁盘本身不足现象是一样的。USE master; GO CREATE DATABASE TestLogFull ON PRIMARY ( NAME NTestLogFull, FILENAME ND:\DBFile\TestLogFull\TestLogFull.mdf, SIZE 500MB, MAXSIZE UNLIMITED, FILEGROWTH 100MB ) LOG ON ( NAME NTestLogFull_log, FILENAME ND:\DBFile\TestLogFull\TestLogFull_Log.ldf, SIZE 1MB, MAXSIZE 512MB, FILEGROWTH 100MB ); GO这里把日志文件最大大小设置为 512MBFILEGROWTH设为 100MB意思是每次日志空间不足自动增长 100MB但总大小不能超过 512MB。如果不设置MAXSIZE在磁盘空间不设限的情况下日志会一直增长到把磁盘写满并不会触发 “log full” 报错所以复现这个现象依赖日志大小限制。接着启用数据库级别的 CDC并创建一张测试表USE TestLogFull; GO EXECUTE sys.sp_cdc_enable_db; GO CREATE TABLE dbo.test_cdc ( id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(50), mail VARCHAR(50), address NVARCHAR(50), lastupdatetime DATETIME ); GO EXEC sys.sp_cdc_enable_table source_schema dbo, source_name test_cdc, role_name cdc_admin, capture_instance DEFAULT, supports_net_changes 1, index_name NULL, filegroup_name DEFAULT; GOsp_cdc_enable_table里的supports_net_changes设为 1 时SQL Server 会额外维护一个dbo_test_cdc_CT表的合并更新机制用来支持cdc.fn_cdc_get_net_changes_dbo_test_cdc这类查询接口同时也意味着捕获作业需要处理两处元数据日志消费路径会比只记录净变更时稍长。capture_instance保持 DEFAULT 时捕获实例的命名规则是架构名_表名也就是dbo_test_cdc。CDC 开启成功后会在系统表中生成对应的捕获实例信息同时创建cdc.dbo_test_cdc_CT变更表。3.2 关闭 Agent 制造日志积压现象SQL Server Agent 是执行捕获作业的宿主进程。关闭 Agent 后日志写入照常发生但cdc.dbo_test_cdc_capture作业无法运行Replication 状态的日志无法被消费。用一个循环插入脚本模拟持续写入USE TestLogFull; GO SET NOCOUNT ON; DECLARE i INT 1; DECLARE batch INT 5000; WHILE i 100000 BEGIN INSERT INTO dbo.test_cdc (name, mail, address, lastupdatetime) VALUES (CONCAT(user_, i), CONCAT(user_, i, example.com), CONCAT(address_, i), GETDATE()); SET i i 1; IF (i % batch 0) BEGIN CHECKPOINT; END END GOCHECKPOINT的作用是触发 checkpoint 截断逻辑。在简单恢复模式下checkpoint 会刷新脏页并尝试截断可重用的日志空间如果部分日志被标记为 Replication则这部分仍然保留而其他非 Replication 部分可以正常重用。在 Agent 服务关闭的场景下日志会持续积压在 “状态为 2” 的 VLF 中直到日志文件达到设定的最大大小 512MB此时所有写入操作都会被阻塞报错信息就会是 “The transaction log for database is full due to ‘REPLICATION’”。此时执行查询确认日志等待状态SELECT log_reuse_wait_desc FROM sys.databases WHERE name TestLogFull;返回结果是REPLICATION就可以确认是捕获作业没有消费日志导致的。执行DBCC SHRINKFILE也不会有效果因为日志尾部空间被活动日志占满收缩操作本身就需要日志尾部可重用空间的支持。3.3 用 sp_repldone 应急释放日志空间启动 SQL Server Agent 服务后你会发现捕获作业依然无法执行原因很直接捕获作业本身也需要写日志而此时日志文件已经没有可用空间。这种情况下只能手动将待复制的日志标记为已分发。USE TestLogFull; GO EXEC sys.sp_repldone xactid NULL, xact_seqno NULL, numtrans 0, time 0, reset 1; GOreset 1表示将所有已复制但尚未标记完成的事务日志全部标记为已分发xactid和xact_seqno设置为 NULL 时配合reset 1使用表示不针对某个具体事务而是重置整条日志分发标记。numtrans 0表示不限定具体事务数量。执行完成后日志空间的释放不会立即发生。此时执行一条写入语句比如插入一行无关紧要的数据触发 checkpoint 后日志空间就会被截断释放。验证方法DBCC LOGINFO(TestLogFull);执行后观察输出的Status列如果大量 VLF 的 Status 已经是 0说明空间已恢复为可重用状态。提示这个操作是应急方案不是常规维护手段。手动标记为已分发的日志对应的 CDC 变更记录并不会补写到变更表CDC 数据链路会出现断裂下游拿不到这部分变化数据。4. 短时间大批量写入导致日志积压的第二个场景4.1 大事务让日志写入速度超过捕获消费速度第二个场景和 Agent 无关。即便 SQL Server Agent 正常运行捕获作业也会因为调度频率、日志读取速度等原因在高吞吐写入时出现消费速度跟不上产生速度的情况。捕获作业读取日志的机制是轮询默认每 5 秒扫描一次日志如果写入量远大于 5 秒内能处理完的量Replication 状态的日志就会积累。日常生产环境最常遇到的问题其实就是这类问题典型场景是数据迁移、批量导入、初始化同步等。当日志文件大小被限制时只要写入速度持续高于捕获作业的消费速度日志文件就会在某个时间点被写满。后续表现和第一个场景一样Agent 作业会因为日志空间不足而失败进而是死循环日志满 → 作业失败 → 日志无法释放 → 后续写入全部阻塞。应对这个场景添加日志文件或扩大日志文件上限是直接有效的办法核心是让捕获作业先跑起来把日志消费到安全水位后再做收缩或调整。USE master; GO ALTER DATABASE TestLogFull ADD LOG FILE ( NAME NTestLogFull_log2, FILENAME ND:\DBFile\TestLogFull\TestLogFull_Log2.ldf, SIZE 1024MB, MAXSIZE UNLIMITED, FILEGROWTH 256MB ); GO执行ADD LOG FILE后日志文件空间立即生效不需要重启服务。这里初始大小给 1024MB是为了确保在捕获作业积压量比较大的情况下作业有足够空间完成日志解析和写入操作。MAXSIZE建议在生产环境保持UNLIMITED如果无法做到不限大小至少要保证磁盘剩余空间大于日志文件当前大小的 1.5 倍给缓冲留出余地。执行完添加日志文件的语句之后需要等待一段时间让捕获作业继续工作。日志解析是逐步进行的积压的日志量越大作业运行时间越长。观察以下查询的输出变化SELECT instance_name capture_instance, start_time start_time, last_commit_time last_commit_time, row_count row_count FROM cdc.dbo_test_cdc_CT;如果row_count持续增加说明捕获作业正在消费日志。注意不要在这期间盲目执行SHRINKFILE因为日志文件仍然包含尚未完全释放的 VLF收缩动作会打断捕获作业的处理节奏。4.2 日志文件增长被磁盘空间限制时的处理顺序如果日志满的原因不是文件大小上限而是磁盘剩余空间不足先观察磁盘空间EXEC sys.xp_readerrorlog 0, 1, Ntransaction log, Nfull;这条命令会读取 SQL Server 错误日志中包含关键字 “transaction log” 或 “full” 的记录能看到日志文件是否触发了自动增长失败。如果磁盘确实没有剩余空间先压缩或迁移其他非必要文件腾出空间再执行上面的ADD LOG FILE。DBCC SQLPERF(LOGSPACE)可以快速查看所有数据库的日志文件使用率DBCC SQLPERF(LOGSPACE);返回结果中的Log Space Used (%)和Log Size (MB)用于判断哪些数据库的日志已经接近上限。如果某库的日志使用率长期高于 90%并且log_reuse_wait_desc是REPLICATION就需要优先排查 CDC 作业状态。4.3 重启服务的边际效果说明在 SQL Server 2014 SP2 及以上版本中如果存放日志的磁盘有可用空间重启 SQL Server 服务后日志文件会自动扩展一小段空间物理大小可能超过MAXSIZE设置。这个现象是服务重启过程中日志文件初始化逻辑导致的不能依赖它作为解决方案。问题在于如果 Agent 没有设置为随服务自动启动重启服务后日志积压问题会重复出现。正确做法是在服务配置里确认 SQL Server Agent 的启动模式是“自动”同时确保 CDC 捕获作业处于启用状态。5. 巡检脚本和积压判断技巧提前发现 REPLICATION 状态日志堆积5.1 快速定位风险库生产环境不建议等日志满后再处理维护窗口内跑一遍巡检脚本可以在日志占比达到 70% 到 80% 时提前发现风险。SELECT d.name AS dbname, d.log_reuse_wait_desc, d.recovery_model_desc, usg.total_log_size_mb, usg.used_log_size_mb, usg.used_log_percent, d.is_cdc_enabled FROM sys.databases d CROSS APPLY sys.dm_db_log_space_usage(d.database_id) usg WHERE d.is_cdc_enabled 1 ORDER BY usg.used_log_percent DESC;sys.dm_db_log_space_usage返回数据库日志文件的总大小、已使用大小和使用百分比比DBCC SQLPERF(LOGSPACE)更精确且支持CROSS APPLY过滤。is_cdc_enabled过滤出所有开启 CDC 的库避免在非 CDC 库上做无效排查。used_log_percent超过 80 且log_reuse_wait_desc为REPLICATION时需要立即处理。如果log_reuse_wait_desc是NOTHING说明日志空间处于完全可重用状态此时即便used_log_percent偏高也只是物理文件大小问题不在本文讨论范围内。5.2 检查捕获作业本身的状态CDC 作业卡死后捕获作业的历史记录里通常会有错误信息。可以通过以下方式检查作业当前状态和最近运行时间SELECT j.name AS job_name, ja.start_execution_date, ja.last_executed_step_id, ja.stop_execution_date, ja.last_executed_step_date, CASE WHEN ja.job_id IS NULL THEN not running WHEN ja.stop_execution_date IS NULL THEN running ELSE not running END AS job_status FROM msdb.dbo.sysjobs j LEFT JOIN msdb.dbo.sysjobactivity ja ON ja.job_id j.job_id WHERE j.name LIKE Ncdc.%;sysjobactivity里的start_execution_date和stop_execution_date用于判断作业是否正在运行。如果某 CDC 作业一直处于 running 状态且长达数十分钟未结束说明它在等待日志空间或者被阻塞。关闭 Agent 再启动后作业大概需要 1 到 2 分钟才能重新调度不要立即判断作业失效。5.3 关联 CDC 变更表的数据增长确认流程当日志积压时CDC 变更表的数据量不会增长这两者之间存在强关联。用这条查询确认积压是否已被消费SELECT capture_instance, COUNT(*) AS captured_row_count FROM cdc.dbo_test_cdc_CT GROUP BY capture_instance;如果写入基表的数据量明显增加但captured_row_count没有变化说明捕获作业积压仍未释放。正常状态下基表写入后几秒内变更表就应该出现对应数据。在批量写入场景下这个延迟会被放大但延迟一般不会超过几分钟长时间不增长就说明从日志读取到变更表写入的链路中断了。一条完整的应急处置顺序是先确认log_reuse_wait_desc是REPLICATION然后确认 CDC 作业是否在空转或处于失败状态接着确认磁盘剩余空间按顺序执行启动无效则添加日志文件日志文件不足则用sp_repldone应急释放空间最后等 CDC 作业恢复运行后再重新评估 VLF 分布和日志文件大小设置。本文还有配套的精品资源点击获取
