SQL Server中的事务日志管理的阶梯，6级：管理大容量日志恢复模型中的日志-CSDN博客

由Gail Shaw，托尼·戴维斯，2012/11/07
该系列

本文是SQL Server中“Stairway系列：事务日志管理的阶梯”的一部分

当事情进展顺利的时候，没有必要特别意识到事务日志的作用或工作原理。你只需要确信每个数据库都有正确的备份机制。当事情出错时，对事务日志的理解对于采取纠正措施是非常重要的，特别是在需要时间点恢复数据库的情况下，迫切需要！托尼·戴维斯（Tony Davis）给出了每个DBA应该知道的正确的细节级别。

这个标题有点用词不当，因为从长远的意义上讲，我们通常不会通过在BULK_LOGGED恢复模式中操作数据库来管理日志。但是，例如，在批量加载操作期间，DBA可能会考虑在短期内将数据库切换到BULK_LOGGED恢复模式。当一个数据库在BULK_LOGGED模型中运行时，以及一些其他操作（如索引重建）可以被最低限度地记录下来，因此会在日志中占用更少的空间。在为大型表重新构建聚簇索引时，或者在批量加载数百万行数据时，与FULL恢复模式相比，在BULK_LOGGED恢复模式下运行时日志空间使用率的降低可能非常大。

但是，我们应该只在充分了解其对数据库还原和恢复的影响后才使用BULK_LOGGED恢复。例如，不可能在包含与最小化日志操作相关的日志记录的日志备份中恢复到特定时间点。此外，还有一种特殊情况，如果在数据库以BULK_LOGGED恢复模式运行时记录的最小日志记录操作存在于事务日志的活动部分中，并且数据文件由于灾难（如磁盘故障）。

如果您的运气在这样的灾难发生时间方面没有了，那么这些限制中的任何一个都可能导致数据丢失。检查有问题的数据库的服务级别协议（SLA），以查看可接受的数据丢失级别;如果它表示零容忍，那么使用BULK_LOGGED模型即使在短时间内也是不可接受的。相反，当然，如果这样的数据库受到定期索引重建或批量加载的影响，那么数据库所有者必须了解对于该数据库的日志空间分配的影响，以及在完全恢复模型下执行这些操作。

说到这一切，对于许多数据库来说，切换到BULK_LOGGED恢复的能力，使SQL Server将最低限度地记录某些操作，是对抗过度日志增长的一个非常有用的武器。在大多数情况下，SLA将允许有足够的余地使其使用成为可以接受的，并且在仔细的计划和程序下，风险将是最小的。

这个级别将包括：

    我们所说的“最小化伐木”
    在日志空间使用方面，最小化日志记录的优点
    最小日志记录对崩溃恢复的影响，时间点恢复以及尾部日志备份
    使用BULK_LOGGED恢复的最佳实践。
图6.8：数据库备份时间表

第五次日志备份的时间跨度是从10:00到10:30。在10:10，BULK INSERT命令（1）加载了一组数据。这个批量数据加载完成并不困难，但是在10:20的一个不相关的事件中，用户进行了“流氓”数据修改（2），关键数据丢失了。项目经理通知DBA团队，并要求他们在10点20分之前将数据库恢复到导致数据丢失的事务开始之前的某个时间点。

在完全恢复模型数据库中，这不是问题。批量数据加载已完全记录，我们可以将数据库恢复到该日志文件中的任何时间点。我们只是简单地恢复最后的完整数据库备份，而不进行恢复，然后将日志文件应用到发生不幸数据丢失事件之前的时间点，使用带有STOPAT参数的RESTORE LOG命令，在10： 20。

在BULK_LOGGED数据库中，我们有一个问题。我们可以在前四个日志备份中的任何时间点进行恢复，但不能在第五个日志备份中的任何时间点（包含最少记录的操作）进行恢复。请记住，对于此日志备份，我们只有受日志最小化操作影响的扩展区，因为它们是在日志备份时存在的。第五个日志备份的恢复是“全部或全部”：或者我们不在这个日志文件中应用任何操作，在第四个文件结束时停止恢复，或者我们全部应用恢复到文件，或继续在第六个日志备份中恢复到任何时间点。

如果我们试图恢复第五个日志备份，在STOPAT 10:15（最小日志操作和流氓修改之间的一段时间），SQL不会去执行日志备份的其余部分，找出需要撤消的操作受最小日志操作影响的页面。它有一个更简单的反应：

Msg 4341，Level 16，State 1，Line 2
此日志备份包含批量更改日志。它不能用于在任意时间点停止。

不幸的是，如果我们应用整个第五个日志文件备份，这将会失败恢复的目的，因为错误的进程在该日志备份文件的某个地方提交了它的更改，所以我们只是删除我们尝试的数据去取回！我们别无选择，只能恢复到第四个日志的结尾，恢复数据库，并报告在此之后所做的任何数据更改的丢失。

如果在日志时间间隔内有最少的记录操作，则无法将数据库恢复到时间点，这是在BULK_LOGGED恢复中选择运行数据库时必须考虑的事情，无论是短期还是长时间。很容易确定特定的日志备份是否包含任何最低限度的日志操作。 RESTORE HEADERONLY返回有关备份的详细信息，包括HasBulkLoggedData列。另外，msdb的备份集表有一个has_BULK_LOGGED_data列。如果列值为1，则日志备份包含最少记录的操作，只能完全恢复或根本不恢复。也就是说，在规划或执行还原时找出这个问题可能是一个不愉快的惊喜。
日志备份大小

需要将影响最小的日志操作的页面复制到日志备份中会影响日志备份的大小。从本质上讲，这意味着虽然BULK_LOGGED恢复数据库中的批量操作的实际日志将会减少，但与完整的恢复数据库相比，日志备份的大小不会更小，并且有时可能会大于可比的日志备份恢复数据库。

要查看最小化日志操作的可能影响，以及BULK_LOGGED恢复模式对日志备份大小的影响，我们将看一个简单的示例。首先，重新运行代码清单6.3中的部分，删除并重新创建BulkLoggedRecovery数据库，将恢复模型设置为BULK_LOGGED，然后进行完整的数据库备份。接下来运行代码6.6，在SomeTable中创建500K行。
使用BulkLoggedRecovery
走
IF OBJECT_ID（'dbo.SomeTable'，'U'）不为NULL
    DROP TABLE dbo.SomeTable;
SELECT TOP 500000
        SomeCol = REPLICATE（'a'，2000）
INTO dbo.SomeTable
From sys.all_columns ac1
        CROSS JOIN sys.all_columns ac2;
go

代码6.6：在BulkLoggedRecovery数据库的SomeTable中插入500K行。

接下来，我们检查当前的日志空间使用情况，然后备份日志。

DBCC SQLPERF(LOGSPACE) ;
--24 MB

--truncate the log
USE master
GO
BACKUP LOG BulkLoggedRecovery
TO DISK = 'D:\SQLBackups\BulkLoggedRecovery_log.trn'
WITH INIT
GO
清单6.7：备份BulkLoggedRecovery的日志。

鉴于日志大小只有24 MB，您可能会惊讶地看到日志备份的大小，在我的测试中大约有1 GB！对于完整恢复中的数据库，您会发现日志大小和日志备份大小均为大约1 GB。
尾巴日志备份

假设硬件故障会导致一些数据损坏，但是数据库仍处于联机状态，我们希望通过该数据库进行恢复。使用BACKUP LOG ... WITH NORECOVERY执行尾部日志备份将捕获日志文件的剩余内容，并将数据库置于还原状态，以便不会再有针对该数据库的事务成功，我们可以开始还原操作。这种尾部日志备份以及正常的日志备份要求数据库联机（以便SQL Server可以将关于日志备份的信息标记到数据库头部中）。

但是，假设数据文件的损坏严重到数据库变得不可用，并且尝试将其恢复联机失败。如果数据库处于FULL恢复模式，并且使用常规日志备份，那么只要日志文件仍然可用，我们就可以采用尾部日志备份，但是使用NO_TRUNCATE选项（即BACKUP LOG ... WITH NO_TRUNCATE）。此操作备份日志文件而不截断它，并不要求数据库联机。

使用现有的备份（完整的，可能是差异的，然后是日志）和尾部日志备份，我们可以将数据库恢复到失败的确切位置。我们可以这样做，因为日志包含足够的信息来重新创建所有提交的事务。

但是，如果数据库处于BULK_LOGGED恢复状态，并且在事务日志的活动部分中存在最低限度的日志记录操作，则日志不会包含足够的信息来重新创建所有已提交的事务，并且实际的数据页需要可用当我们采取尾巴日志备份。如果数据文件不可用，则该日志备份无法复制它需要恢复一致数据库所需的数据页面。让我们看看这个在使用BulkLoggedRecovery数据库的行动。
BACKUP DATABASE BulkLoggedRecovery
    TO DISK = 'D:\SQLBackups\BulkLoggedRecovery2.bak'
WITH INIT
GO
USE BulkLoggedRecovery
GO

IF OBJECT_ID('dbo.SomeTable', 'U') IS NOT NULL
    DROP TABLE dbo.SomeTable ;
SELECT TOP 200
        SomeCol = REPLICATE('a', 2000)
INTO    dbo.SomeTable
FROM    sys.all_columns ac1
GO

SHUTDOWN WITH NOWAIT
代码6.8：创建BulkLoggedRecovery数据库，执行一个SELECT INTO，然后关闭。

在关闭SQL Server服务的情况下，转到数据文件夹并删除BulkLoggedRecovery数据库的mdf文件，然后重新启动SQL Server。这不是一个驱动器故障的完整模拟，但它足够接近本演示的目的。

当SQL Server重新启动时，数据库不可用，这是不足为奇的，因为它的主数据文件丢失了。状态是Recovery_Pending，这意味着SQL无法打开数据库来运行崩溃恢复。
USE master
GO
SELECT name ,
        state_desc
FROM    sys.databases
WHERE   name = 'BulkLoggedRecovery'

name                         state_desc
---------------------------------------
BulkLoggedRecovery     RECOVERY_PENDING
代码6.9：BulkLoggedRecovery数据库处于Recovery_Pending状态。

在清单6.10中，我们尝试进行尾部日志备份（请注意，NO_TRUNCATE意味着COPY_ONLY和CONTINUE_AFTER_ERROR）：
BACKUP LOG BulkLoggedRecovery
TO DISK = 'D:\SQLBackups\BulkLoggedRecovery_tail.trn'
WITH NO_TRUNCATE

Processed 7 pages for database 'BulkLoggedRecovery', file 'BulkLoggedRecovery_log' on file 1.
BACKUP WITH CONTINUE_AFTER_ERROR successfully generated a backup of the damaged database. Refer to the SQL Server error log for information about the errors that were encountered.
BACKUP LOG successfully processed 7 pages in 0.007 seconds (7.463 MB/sec).
清单6.10：使用BACKUP LOG ... WITH NO_TRUNCATE试图进行尾部日志备份。

那么，它说它成功了（尽管警告，错误日志中没有错误）。现在，我们试着恢复这个数据库，如清单6.11所示。
RESTORE DATABASE BulkLoggedRecovery
FROM DISK = 'D:\SQLBackups\BulkLoggedRecovery2.bak'
WITH NORECOVERY

RESTORE LOG BulkLoggedRecovery
FROM DISK = 'D:\SQLBackups\BulkLoggedRecovery_tail.trn'
WITH RECOVERY

Processed 184 pages for database 'BulkLoggedRecovery', file 'BulkLoggedRecovery' on file 1.
Processed 3 pages for database 'BulkLoggedRecovery', file 'BulkLoggedRecovery_log' on file 1.
RESTORE DATABASE successfully processed 187 pages in 0.043 seconds (33.895 MB/sec).

Msg 3182, Level 16, State 2, Line 5
The backup set cannot be restored because the database was damaged when the backup occurred. Salvage attempts may exploit WITH CONTINUE_AFTER_ERROR.
Msg 3013, Level 16, State 1, Line 5
RESTORE LOG is terminating abnormally.
清单6.11：试图恢复BulkLoggedRecovery。
RESTORE DATABASE BulkLoggedRecovery
FROM DISK = 'D:\SQLBackups\BulkLoggedRecovery2.bak'
WITH NORECOVERY

RESTORE LOG BulkLoggedRecovery
FROM DISK = 'D:\SQLBackups\BulkLoggedRecovery_tail.trn'
WITH RECOVERY, CONTINUE_AFTER_ERROR

Processed 184 pages for database 'BulkLoggedRecovery', file 'BulkLoggedRecovery' on file 1.
Processed 3 pages for database 'BulkLoggedRecovery', file 'BulkLoggedRecovery_log' on file 1.
RESTORE DATABASE successfully processed 187 pages in 0.037 seconds (39.392 MB/sec).
Processed 0 pages for database 'BulkLoggedRecovery', file 'BulkLoggedRecovery' on file 1.
Processed 7 pages for database 'BulkLoggedRecovery', file 'BulkLoggedRecovery_log' on file 1.
The backup set was written with damaged data by a BACKUP WITH CONTINUE_AFTER_ERROR.
RESTORE WITH CONTINUE_AFTER_ERROR was successful but some damage was encountered. Inconsistencies in the database are possible.
RESTORE LOG successfully processed 7 pages in 0.013 seconds (4.018 MB/sec).
列表6.12：使用CONTINUE_AFTER_ERROR恢复日志备份。

这工作，所以让我们调查恢复的数据库的状态。重新运行列表6.9，你会看到它被报告为ONLINE，并且SomeTable，SELECT ... INTO的目标存在，所以让我们看看是否有任何数据进入表（记住，页面分配记录，内容页面不是）。
USE BulkLoggedRecovery
GO
IF OBJECT_ID('dbo.SomeTable', 'U') IS NOT NULL
    PRINT 'SomeTable exists'

SELECT *
FROM    SomeTable
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:184; actual 0:0). It occurred during a read of page (1:184) in database ID 32 at offset 0x00000000170000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\BulkLoggedRecovery.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
代码清单6.13：试图读取SomeTable。

请注意，这是SQL Server 2008企业版的错误消息。有可能你会在其他版本上看到不同的错误。无论如何，这看起来不太好，让我们看看DBCC CHECKDB对数据库状态的看法。
DBCC CHECKDB ('BulkLoggedRecovery') WITH NO_INFOMSGS, ALL_ERRORMSGS

Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
代码清单6.14：用DBCC CHECKDB检查BulkLoggedRecovery的状态。

这不好看，不幸的是（和TempDB没有太空）。关于这里唯一明智的选择是再次恢复并停止尾部日志备份。这意味着在最近的正常日志备份和故障点之间进行的任何事务都将丢失。

在决定在BULK_LOGGED恢复模式中长期或短期运行数据库时，这是另一个重要的考虑因素。在完全恢复模式中，尾部日志备份只需要访问事务日志。因此，即使MDF文件由于磁盘故障而不可用，我们也可以备份事务日志。但是，在BULK_LOGGED模型中，如果自上次日志备份以来发生任何最低限度的日志操作，则意味着如果包含受最小日志操作影响的数据的数据文件不可用，则无法执行尾日志备份。这样做的原因是，在BULK_LOGGED模型中执行事务日志备份时，SQL Server必须将事务日志备份文件备份到批量操作修改的所有实际扩展数据块（即数据）以及事务日志条目。换句话说，SQL Server需要访问数据文件才能进行尾部日志备份。
使用BULK_LOGGED的最佳实践

首先，检查数据库的SLA是否存在可接受的数据丢失风险。如果没有数据丢失是可以接受的，那么计划在FULL恢复模式下执行所有的操作，这对日志增长有影响。如果您可以按照SLA中的规定完成您希望最小程度地记录最大允许数据丢失的操作，则可以考虑使用BULK_LOGGED恢复。

使用BULK_LOGGED恢复模式的黄金法则是尽可能短地使用它，并尽可能地尝试将最小化日志操作隔离到其自己的日志备份中。因此，在切换到BULK_LOGGED恢复之前立即执行事务日志备份，并在切换回FULL恢复时立即执行另一个事务日志备份。这将确保尽可能少的时间和最小数量的事务处于包含最少日志操作的日志间隔内。

为了说明如何降低风险，请考虑以下情形：

    凌晨1:00完全备份
    上午1点15分交易日志备份1
    上午2:15事务日志备份2
    上午2点40分切换到BULK_LOGGED，批量操作开始
    凌晨3点05分散装作业结束
    凌晨3点10分 - 失败 - 中密度纤维板无法使用
    上午3:15事务日志备份3

在这种情况下，3.15 a.m.日志备份将会失败，后续尝试执行尾部日志备份也会失败。我们所能做的就是恢复完整的备份，然后是前两个日志备份，所以我们会损失55分钟的数据。

相反，如果我们采取了下面的制度，那么我们的状况会好得多：

    凌晨1:00完全备份
    上午1点15分交易日志备份1
    上午2:15事务日志备份2
    上午2:35事务日志备份3
    上午2点40分切换到BULK_LOGGED，批量操作开始
    凌晨3点05分散装作业结束
    3:05 a.m.切换回FULL并执行事务日志备份4
    凌晨3点10分 - 失败 - 中密度纤维板无法使用
    上午3:15事务日志备份5

在这里，3:15日志备份也会失败，但我们随后可以执行尾日志备份，因为日志备份4确保在活动日志中没有最小化日志操作。然后，我们可以在上午3点15分恢复完整备份，四个事务日志备份和尾部日志备份以恢复到故障点。

即使有了这些预防性日志备份，最好在非常少的（如果有的话）其他事务正在执行的情况下，在数小时内执行任何最少的日志操作。这样，如果出现任何问题，我们可以简单地重播批量负载以恢复数据。

即使通过在每次批量操作之前和之后进行额外的日志备份来最大限度地降低风险，在BULK_LOGGED模型中连续运行数据库也是不可取的。根据您的环境，可能会非常困难地对可能执行最少日志操作的人员以及何时执行完全控制。请记住，任何表所有者都可以在该表上创建或重建索引;任何可以创建表的人也可以运行SELECT ... INTO语句。

最后，我们建议您阅读“数据加载性能指南”（http://download.microsoft.com/zh-cn/library/dd425070.aspx），该指南提供了有关实现高速批量数据修改的许多建议，并讨论了如何以使用跟踪标志610来测量由最小日志记录引起的潜在益处。
概要

BULK_LOGGED恢复模型提供了一种执行数据加载和一些数据库维护操作（如索引重建）的方法，无需事务日志开销，它们通常在FULL恢复模式中具有b