SQL Server 日志传输还原作业执行缓慢

情景

IP	角色
192.168.1.61	Primary
192.168.1.59	Secondary

为 db1~db6 共 6 个数据库搭建日志传输。日志传输中的每个从库都都设置成 STANDBY 模式，搭建成功后查看 Primary 和 Secondary 实例上的 事务日志传送状态 报表发现，db1~db5 的备份、复制、还原作业工作良好，而 db6 的备份、复制作业正常，但还原作业执行时间过长,延迟长达10多个小时，并且在不断增加。

在定位是何问题前，务必确保已经排除以下因素：

当前日志备份大小远超平常值
Secondary 服务器上的磁盘未达到其瓶颈
复制作业工作正常且无延迟，从作业历史记录中可明显看出是还原作业消耗大量的时间
还原作业在执行期间未失败、未报错(e.g. Out of Memory etc.)

故障定位

首先，启用以下 trace flags 收集信息，以了解还原工作期间的更多信息，，可参考 How It Works: What is Restore/Backup Doing?

DBCC TRACEON (3004, 3605, -1)

3004 - 提供关于 backup 和 restore 作业的更多信息
3005 - 将 trace 收集到的信息输出到 error log 中

在日志传输中的 Secondary 上启用 trace flags 之后收集到的日志信息

2018-12-07 11:13:53.57 spid56      RestoreDatabase: Database zktime606`
2018-12-07 11:13:53.58 spid56      Opening backup set
2018-12-07 11:13:53.58 spid56      SetTargetRestoreAge: 0
2018-12-07 11:13:53.59 spid56      Restore: Configuration section loaded
2018-12-07 11:13:53.59 spid56      Restore: Backup set is open
2018-12-07 11:13:53.59 spid56      Restore: Planning begins
2018-12-07 11:13:53.61 spid56      Restore: Planning complete
2018-12-07 11:13:53.61 spid56      Restore: BeginRestore (offline) on zktime606
2018-12-07 11:13:53.62 spid56      Restore: Attached database zktime606 as DBID=17
2018-12-07 11:13:53.62 spid56      Restore: PreparingContainers
2018-12-07 11:13:53.70 spid56      Zeroing F:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\zktime606_0.ldf from page 1 to 96 (0x2000 to 0xc0000)
2018-12-07 11:13:53.70 spid56      Restore: Containers are ready
2018-12-07 11:13:53.70 spid56      Zeroing completed on F:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\zktime606_0.ldf
2018-12-07 11:13:53.75 spid56      Restore: Restoring backup set
2018-12-07 11:13:53.75 spid56      Restore: Transferring data to zktime606
2018-12-07 11:13:56.05 spid56      Restore: Waiting for log zero on zktime606
2018-12-07 11:13:56.08 spid56      Restore: LogZero complete
2018-12-07 11:13:56.08 spid56      SetTargetRestoreAge: 0
2018-12-07 11:13:56.56 spid56      FileHandleCache: 0 files opened. CacheSize: 12
2018-12-07 11:13:56.56 spid56      Restore: Data transfer complete on zktime606
2018-12-07 11:13:56.61 spid56      Restore: Backup set restored
2018-12-07 11:13:56.64 spid56      Starting up database 'zktime606'.
2018-12-07 11:13:56.70 spid56      The database 'zktime606' is marked RESTORING and is in a state that does not allow recovery to be run.
2018-12-07 11:13:56.75 spid56      Restore-Redo begins on database zktime606
2018-12-07 11:13:56.77 spid56      RunOfflineRedo logIter.Init(): FirstLsn(PruId: 0): 0x2e9e:0xa0:0x26
2018-12-07 11:13:56.77 spid56      RunOfflineRedo logIter.Init(): LastLsn(PruId: 0): 0x2e9e:0xc0:0x1
2018-12-07 11:13:56.77 spid56      OfflineRollforward: StopLsn/LastLsn(PruId: 0): 0x2e9e:0xc0:0x1
2018-12-07 11:13:56.79 spid56      Rollforward complete on database zktime606
2018-12-07 11:13:56.90 spid56      Restore: Done with fixups
2018-12-07 11:13:56.99 spid56      Restore: Writing history records
2018-12-07 11:13:56.99 备份          Database was restored: Database: zktime606, creation date(time): 2018/12/05(15:41:54), first LSN: 11934:160:38, last LSN: 11934:192:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'F:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\FB_zktime.bak'}). Informational message. No user action required.
2018-12-07 11:13:56.99 spid56      Writing backup history records
2018-12-07 11:13:57.02 spid56      Restore: Done with MSDB maintenance
2018-12-07 11:13:57.02 spid56      RestoreDatabase: Finished

从上面的错误日志输出中可得

还原过程花费了 2018-12-07 11:13:57.02 - 2018-12-07 11:13:53.57 = 4s
根据每行日志记录中第一列的时间戳查出该过程中哪个阶段花费时间最长

VLF 对 Restore 的影响

数据库事务日志中虚拟日志文件过多会导致日事务日志还原缓慢，请参考数据库日志文件结构对数据库还原时间的影响,为确定是否是该因素导致的，使用以下命令

dbcc loginfo (dbname) with no_infomsgs

以下信息是为上述命令的输出

1 上述命令输出的行数就是该数据库事务日志文件包含的虚拟日志文件数目
SQL Server 日志传输还原作业执行缓慢

2 在备份日志中需要被还原的 VLFs 的数量由以下公式计算。

first LSN: 11934:160:38, last LSN: 11934:192:1,

last LSN 的第一部分减去 first LSN 的第一部分 = 11934 - 11934

3 基于 FileSize 列计算 VLF 文件的大小

9175040 – 8.75 MB 
9437184 – 9 MB 
10092544 – 9.62 MB

问题

基于上述输出的信息，有两种可能性

VLF's 文件过多从而影响辅助数据库还原性能
如果模式是 STANDBY ，且每个VLF文件都很大，则这是一个值得关注的问题，。

如果存在跨越多个备份的批处理作业或长时间运行的事务(例如，重建索引)，则第二个问题更加严重。在这种情况下，重复回滚长期运行的事务，将回滚工作写入 standby 文件(.tuf文件)，然后通过下一次日志还原取消所有回滚工作，只为了重新启动进程，将很容易地导致日志传送中的辅助数据库落后。

解决方案

方案1

你得减少VLF的数量。可以通过运行DBCC SHRINKFILE来将LDF减少到较小的大小，从而减少VLFS的数量。

注意：你需要在主数据库上执行该操作。

完成收缩后，通过再次运行DBCC LOGINFO验证 VLF已经减少。一个很好的范围应该在 500-1000 之间。使用单个增长操作将日志文件调整到所需大小。可通过调整初始大小设置来做到这一点，还可以注意LDF文件的自动增长设置。设置太小的增长值可能会导致太多的VLFs。

ALTER DATABASE DBNAME MODIFY FILE (NAME='ldf_logical_name', SIZE=<target>MB)

记得在应用保存了收缩数据库日志操作的日志备份之前，辅助数据库仍然必须先应用在此之前的日志备份。一旦辅助数据库应用完保存了收缩日志操作的日志备份，就可以测量辅助数据库恢复事务日志的时间，以观察是否产生了积极的影响。

方案 2

对于问题 2 的解决方案 2 ，当VLF文件的大小对在STANDBY 模式下的还原造成较大影响时，你将不得不截断事务日志。这意味着日志传输必须中断。可以通过将恢复模型设置为SIMPLE(使用 ALTER DATABASE 命令)来截断主数据库上的TLOG。如果在 SQL 2005或更低版本上，可以使用带有TRUNCATE_ONLY选项的backup log dbName。然后将日志文件自动增长设置为适当的值。注意你在这里设置的值不要太高，否则事务必须在文件增长时等待或者太低以至于它创建了太多的VLF。立即进行完整数据库备份，并使用此备份重新设置日志传送。