sqlserver-事物日志

前言

每个 SQL Server 数据库都有事务日志，用于记录所有事务以及每个事务所做的数据库修改。

事务日志是数据库的一个关键组件。 如果系统出现故障，你将需要依靠该日志将数据库恢复到一致的状态。

有关事务日志体系结构和内部组件的详细信息，请参阅 SQL Server 事务日志体系结构和管理指南。

警告

永远不要删除或移动此日志，除非你完全了解执行此操作的后果。

提示

检查点会创建一些正常点，在数据库恢复期间将从这些正常点开始应用事务日志。 有关详细信息，请参阅数据库检查点 (SQL Server)。

事务日志逻辑体系结构

SQL Server 事务日志按逻辑运行，就好像事务日志是一串日志记录一样。 每条日志记录由一个日志序列号 (LSN) 标识。 每条新日志记录均写入日志的逻辑结尾处，并使用一个比前面记录的 LSN 更高的 LSN。 日志记录按创建的顺序存储：如果 LSN2 大于 LSN1，则 LSN2 所标识的日志记录描述的更改发生在日志记录 LSN1 描述的更改之后。 每条日志记录都包含其所属事务的 ID。 对于每个事务，与事务相关联的所有日志记录通过使用可提高事务回滚速度的向后指针挨个链接在一个链中。

数据修改的日志记录或者记录所执行的逻辑操作，或者记录已修改数据的前像和后像。 前像是执行操作前的数据副本；后像是执行操作后的数据副本。

操作的恢复步骤取决于日志记录的类型：

• 记录逻辑操作

若要前滚逻辑操作，请再次执行该操作。

若要回滚逻辑操作，请执行相反的逻辑操作。

• 记录前像和后像

若要前滚操作，请应用后像。

若要回滚操作，请应用前像。

许多类型的操作都记录在事务日志中。 这些操作包括：

• 每个事务的开始和结束。
• 每次数据修改（插入、更新或删除）。 这包括系统存储过程或数据定义语言 (DDL) 语句对包括系统表在内的任何表所做的更改。
• 每次分配或释放区和页。
• 创建或删除表或索引。

回滚操作也记录在日志中。 每个事务都在事务日志中保留空间，以确保存在足够的日志空间来支持由显式回滚语句或遇到错误引起的回滚。 保留的空间量取决于在事务中执行的操作，但通常等于用于记录每个操作的空间量。 事务完成后将释放此保留空间。

日志文件中从必须存在以确保数据库范围内成功回滚的第一条日志记录到最后写入的日志记录之间的部分称为日志的活动部分，即“活动日志”或“日志尾部” 。 这是进行数据库完整恢复所需的日志部分。 永远不能截断活动日志的任何部分。 此第一条日志记录的日志序列号 (LSN)，称为最小恢复 LSN (MinLSN) 。 有关事务日志支持的操作的详细信息，请参阅事务日志 (SQL Server)。
我的理解为：即最后一条能成功回滚的日志记录（MinLSN）和最后的日志记录之间的部分叫做日志尾部或活动日志。
差异和日志备份将还原的数据库推到稍后的时间，该时间与一个更高的 LSN 相对应。

事务日志物理体系结构

数据库中的事务日志映射在一个或多个物理文件上。 从概念上讲，日志文件是一系列日志记录。 从物理上讲，日志记录序列被有效地存储在实现事务日志的物理文件集中。 每个数据库必须至少有一个日志文件。

虚拟日志文件 (VLF)

SQL Server 数据库引擎 在内部将每物理日志文件分成多个虚拟日志文件 (VLF)。 虚拟日志文件没有固定大小，且物理日志文件所包含的虚拟日志文件数不固定。 数据库引擎 在创建或扩展日志文件时动态选择虚拟日志文件的大小。 数据库引擎 尝试维护少量的虚拟文件。 在扩展日志文件后，虚拟文件的大小是现有日志大小和新文件增量大小之和。 管理员不能配置或设置虚拟日志文件的大小或数量。

备注

虚拟日志文件 (VLF) 的创建遵循此方法：

如果下一次增长少于当前日志物理大小的 1/8，则创建 1 个 VLF，补偿此增长大小（从 SQL Server 2014 (12.x)
开始） 如果下一次增长超过当前日志大小的 1/8，则使用 pre-2014 方法： 如果增长少于 64 MB，创建 4 个
VLF，补偿此增长大小（如增长 1 MB，创建四个 256KB 的 VLF） 如果增长在 64 MB 到 1GB 之间，创建 8 个
VLF，补偿此增长大小（如增长 512 MB，创建八个 64MB 的 VLF） 如果增长大于 1GB，创建 16 个
VLF，补偿此增长大小（如增长 8 GB，创建十六个 512MB VLF）

如果这些日志文件由于许多微小增量而增长到很大，则它们将具有很多虚拟日志文件。 这会降低数据库启动以及日志备份和还原操作的速度。 相反，如果日志文件设置得较大，但只有少量或仅一个增量，则它们将只有几个非常大的虚拟日志文件。 若要深入了解如何正确估计事务日志的所需大小和自动增长设置，请参阅管理事务日志文件的大小的“建议”部分。

建议为日志文件分配一个接近于最终所需大小的 size 值，使用所需增量实现最佳 VLF 分发，并且还要分配一个相对较大的 growth_increment 值。 请参考以下提示，确定当前事务日志大小的最佳 VLF 分发。

• ALTER DATABASE 的 SIZE 参数设置的 size 值是指日志文件的初始大小。
• ALTER DATABASE 的 FILEGROWTH 参数设置的 growth_increment
  值（也称为自动增长值）是指每次需要新空间时添加到文件的空间大小。

有关和参数的详细信息，请参阅 ALTER DATABASE (Transact-SQL) 文件和文件组选项。FILEGROWTHALTER DATABASESIZE

提示

若要确定给定实例中所有数据库的当前事务日志大小的最佳 VLF 分发，以及实现所需大小需要的增长量，请参阅此脚本。

-- 2011-05-24 Pedro Lopes (Microsoft) pedro.lopes@microsoft.com (http://aka.ms/sqlinsights)
--
-- 2012-03-25 Added SQL 2012 support
-- 2012-09-19 Simplified logic
-- 2012-09-20 Changed grow settings if not SQL Server 2012
--
-- Generates the sql statements to preemtively fix VLF issues in all DBs within the server, based on the transaction log current size.
--
SET NOCOUNT ON;

DECLARE @query VARCHAR(1000), @dbname VARCHAR(255), @count int, @usedlogsize bigint, @logsize bigint
DECLARE @sqlcmd NVARCHAR(1000), @sqlparam NVARCHAR(100), @filename VARCHAR(255), @i int, @recmodel NVARCHAR(128)
DECLARE @potsize int, @n_iter int, @n_iter_final int, @initgrow int, @n_init_iter int, @bckpath NVARCHAR(255)
DECLARE @majorver smallint, @minorver smallint, @build smallint

CREATE TABLE #loginfo (dbname varchar(100), num_of_rows int, used_logsize_MB DECIMAL(20,1))

DECLARE @tblvlf TABLE (dbname varchar(100), 
	Actual_log_size_MB DECIMAL(20,1), 
	Potential_log_size_MB DECIMAL(20,1), 
	Actual_VLFs int, 
	Potential_VLFs int, 
	Growth_iterations int,
	Log_Initial_size_MB DECIMAL(20,1), 
	File_autogrow_MB DECIMAL(20,1))
	
SELECT TOP 1 @bckpath = REVERSE(RIGHT(REVERSE(physical_device_name), LEN(physical_device_name)-CHARINDEX('\',REVERSE(physical_device_name),0))) FROM msdb.dbo.backupmediafamily WHERE device_type = 2

SELECT @majorver = (@@microsoftversion / 0x1000000) & 0xff, @minorver = (@@microsoftversion / 0x10000) & 0xff, @build = @@microsoftversion & 0xffff
 
--DECLARE csr CURSOR FAST_FORWARD FOR SELECT name FROM master..sysdatabases WHERE dbid > 4 AND DATABASEPROPERTYEX(name,'status') = 'ONLINE' AND DATABASEPROPERTYEX(name,'Updateability') = 'READ_WRITE' AND name <> 'tempdb' AND name <> 'ReportServerTempDB'
DECLARE csr CURSOR FAST_FORWARD FOR SELECT name FROM master.sys.databases WHERE is_read_only = 0 AND state = 0 AND database_id <> 2;
OPEN csr
FETCH NEXT FROM csr INTO @dbname
WHILE (@@FETCH_STATUS <> -1)
BEGIN
	CREATE TABLE #log_info (recoveryunitid int NULL,
	fileid tinyint,
	file_size bigint,
	start_offset bigint,
	FSeqNo int,
	[status] tinyint,
	parity tinyint,
	create_lsn numeric(25,0))

	SET @query = 'DBCC LOGINFO (' + '''' + @dbname + ''') WITH NO_INFOMSGS'
	IF @majorver < 11
	BEGIN
		INSERT INTO #log_info (fileid, file_size, start_offset, FSeqNo, [status], parity, create_lsn)
		EXEC (@query)
	END
	ELSE
	BEGIN
		INSERT INTO #log_info (recoveryunitid, fileid, file_size, start_offset, FSeqNo, [status], parity, create_lsn)
		EXEC (@query)
	END
	SET @count = @@ROWCOUNT
	SET @usedlogsize = (SELECT (MIN(l.start_offset) + SUM(CASE WHEN l.status <> 0 THEN l.file_size ELSE 0 END))/1024.00/1024.00 FROM #log_info l)
	DROP TABLE #log_info;
	INSERT #loginfo
	VALUES(@dbname, @count, @usedlogsize);
	FETCH NEXT FROM csr INTO @dbname
END

CLOSE csr
DEALLOCATE csr

PRINT '/* Generated on ' + CONVERT (VARCHAR, GETDATE()) + ' in ' + @@SERVERNAME + ' */' + CHAR(10)
	
DECLARE cshrk CURSOR FAST_FORWARD FOR SELECT dbname, num_of_rows FROM #loginfo 
WHERE num_of_rows >= 50 --My rule of thumb is 50 VLFs. Your mileage may vary.
ORDER BY dbname
OPEN cshrk
FETCH NEXT FROM cshrk INTO @dbname, @count
WHILE (@@FETCH_STATUS <> -1)
BEGIN
	SET @sqlcmd = 'SELECT @nameout = name, @logsizeout = (CAST(size AS BIGINT)*8)/1024 FROM [' + @dbname + '].dbo.sysfiles WHERE (64 & status) = 64'
	SET @sqlparam = '@nameout NVARCHAR(100) OUTPUT, @logsizeout bigint OUTPUT'
	EXEC sp_executesql @sqlcmd, @sqlparam, @nameout = @filename OUTPUT, @logsizeout = @logsize OUTPUT;
	PRINT '---------------------------------------------------------------------------------------------------------- '
	PRINT CHAR(13) + 'USE ' + QUOTENAME(@dbname) + ';'
	PRINT 'DBCC SHRINKFILE (N''' + @filename + ''', 1, TRUNCATEONLY);'
	PRINT '--'
	PRINT '-- CHECK: if the tlog file has shrunk with the following query:'
	PRINT 'SELECT name, (size*8)/1024 AS log_MB FROM [' + @dbname + '].dbo.sysfiles WHERE (64 & status) = 64'
	PRINT '--'
	SET @recmodel = CONVERT(NVARCHAR, DATABASEPROPERTYEX(@dbname,'Recovery'))
	IF @recmodel <> 'SIMPLE' 
	AND SERVERPROPERTY('EngineEdition') <> 8 -- This cannot be applied on Managed Instance
	BEGIN
		PRINT '-- If the log has not shrunk, you must backup the transaction log next.'
		PRINT '-- Repeat the backup and shrink process alternatively until you get the desired log size (about 1MB).'
		PRINT '--'
		PRINT '-- METHOD: Backup -> Shrink (repeat the backup and shrink process until the log has shrunk):'
		PRINT '--'
		PRINT '-- Create example logical backup device.' 
		PRINT 'USE master;' + CHAR(13) + 'EXEC sp_addumpdevice ''disk'', ''BckLog'', ''' + @bckpath + '\example_bck.trn'';'
		PRINT 'USE ' + QUOTENAME(@dbname) + ';'
		PRINT '-- Backup Log'
		PRINT 'BACKUP LOG ' + QUOTENAME(@dbname) + ' TO BckLog;'
		PRINT '-- Shrink'
		PRINT 'DBCC SHRINKFILE (N''' + @filename + ''', 1);'
		PRINT '--'
		PRINT '-- METHOD: Alter recovery model -> Shrink:'
		PRINT '-- NOTE: Because the database is in ' + @recmodel + ' recovery model, one alternative is to set it to SIMPLE to truncate the log, shrink it, and reset it to ' + @recmodel + '.'
		PRINT '-- NOTE2: This method of setting the recovery model to SIMPLE and back again WILL BREAK log chaining, and thus any log shipping or mirroring.'
		PRINT 'USE [master]; ' + CHAR(13) + 'ALTER DATABASE ' + QUOTENAME(@dbname) + ' SET RECOVERY SIMPLE;'
		PRINT 'USE ' + QUOTENAME(@dbname) + ';' + CHAR(13) + 'DBCC SHRINKFILE (N''' + @filename + ''', 1);'
		PRINT 'USE [master]; ' + CHAR(13) + 'ALTER DATABASE ' + QUOTENAME(@dbname) + ' SET RECOVERY ' + @recmodel + ';'
		PRINT '--'
		PRINT '-- CHECK: if the tlog file has shrunk with the following query:'
		PRINT 'SELECT name, (size*8)/1024 AS log_MB FROM [' + @dbname + '].dbo.sysfiles WHERE (64 & status) = 64'
	END
	ELSE
	BEGIN
		PRINT '-- If not, then proceed to the next step (it may be necessary to execute multiple times):'
		PRINT 'DBCC SHRINKFILE (N''' + @filename + ''', 1);'
		PRINT '-- CHECK: if the tlog file has shrunk with the following query:'
		PRINT 'SELECT name, (size*8)/1024 AS log_MB FROM [' + @dbname + '].dbo.sysfiles WHERE (64 & status) = 64'
	END

	-- We are growing in MB instead of GB because of known issue prior to SQL 2012.
	-- More detail here: http://www.sqlskills.com/BLOGS/PAUL/post/Bug-log-file-growth-broken-for-multiples-of-4GB.aspx
	-- and http://connect.microsoft.com/SQLServer/feedback/details/481594/log-growth-not-working-properly-with-specific-growth-sizes-vlfs-also-not-created-appropriately
	-- or https://connect.microsoft.com/SQLServer/feedback/details/357502/transaction-log-file-size-will-not-grow-exactly-4gb-when-filegrowth-4gb
	IF @majorver >= 11
	BEGIN
		SET @n_iter = (SELECT CASE WHEN @logsize <= 64 THEN 1
			WHEN @logsize > 64 AND @logsize < 256 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/256, 0)
			WHEN @logsize >= 256 AND @logsize < 1024 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/512, 0)
			WHEN @logsize >= 1024 AND @logsize < 4096 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/1024, 0)
			WHEN @logsize >= 4096 AND @logsize < 8192 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/2048, 0)
			WHEN @logsize >= 8192 AND @logsize < 16384 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/4096, 0)
			WHEN @logsize >= 16384 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/8192, 0)
			END)
		SET @potsize = (SELECT CASE WHEN @logsize <= 64 THEN 1*64
			WHEN @logsize > 64 AND @logsize < 256 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/256, 0)*256
			WHEN @logsize >= 256 AND @logsize < 1024 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/512, 0)*512
			WHEN @logsize >= 1024 AND @logsize < 4096 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/1024, 0)*1024
			WHEN @logsize >= 4096 AND @logsize < 8192 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/2048, 0)*2048
			WHEN @logsize >= 8192 AND @logsize < 16384 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/4096, 0)*4096
			WHEN @logsize >= 16384 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/8192, 0)*8192
			END)
	END
	ELSE
	BEGIN
		SET @n_iter = (SELECT CASE WHEN @logsize <= 64 THEN 1
			WHEN @logsize > 64 AND @logsize < 256 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/256, 0)
			WHEN @logsize >= 256 AND @logsize < 1024 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/512, 0)
			WHEN @logsize >= 1024 AND @logsize < 4096 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/1024, 0)
			WHEN @logsize >= 4096 AND @logsize < 8192 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/2048, 0)
			WHEN @logsize >= 8192 AND @logsize < 16384 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/4000, 0)
			WHEN @logsize >= 16384 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/8000, 0)
			END)
		SET @potsize = (SELECT CASE WHEN @logsize <= 64 THEN 1*64
			WHEN @logsize > 64 AND @logsize < 256 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/256, 0)*256
			WHEN @logsize >= 256 AND @logsize < 1024 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/512, 0)*512
			WHEN @logsize >= 1024 AND @logsize < 4096 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/1024, 0)*1024
			WHEN @logsize >= 4096 AND @logsize < 8192 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/2048, 0)*2048
			WHEN @logsize >= 8192 AND @logsize < 16384 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/4000, 0)*4000
			WHEN @logsize >= 16384 THEN ROUND(CONVERT(FLOAT, ROUND(@logsize, -2))/8000, 0)*8000
			END)
	END
	
	-- If the proposed log size is smaller than current log, and also smaller than 4GB,
	-- and there is less than 512MB of diff between the current size and proposed size, add 1 grow.
	SET @n_iter_final = @n_iter
	IF @logsize > @potsize AND @potsize <= 4096 AND ABS(@logsize - @potsize) < 512
	BEGIN
		SET @n_iter_final = @n_iter + 1
	END
	-- If the proposed log size is larger than current log, and also larger than 50GB, 
	-- and there is less than 1GB of diff between the current size and proposed size, take 1 grow.
	ELSE IF @logsize < @potsize AND @potsize <= 51200 AND ABS(@logsize - @potsize) > 1024
	BEGIN
		SET @n_iter_final = @n_iter - 1
	END

	IF @potsize = 0 
	BEGIN 
		SET @potsize = 64 
	END
	IF @n_iter = 0 
	BEGIN 
		SET @n_iter = 1
	END
	
	SET @potsize = (SELECT CASE WHEN @n_iter < @n_iter_final THEN @potsize + (@potsize/@n_iter) 
			WHEN @n_iter > @n_iter_final THEN @potsize - (@potsize/@n_iter) 
			ELSE @potsize END)
	
	SET @n_init_iter = @n_iter_final
	IF @potsize >= 8192
	BEGIN
		SET @initgrow = @potsize/@n_iter_final
	END
	IF @potsize >= 64 AND @potsize <= 512
	BEGIN
		SET @n_init_iter = 1
		SET @initgrow = 512
	END
	IF @potsize > 512 AND @potsize <= 1024
	BEGIN
		SET @n_init_iter = 1
		SET @initgrow = 1023
	END
	IF @potsize > 1024 AND @potsize < 8192
	BEGIN
		SET @n_init_iter = 1
		SET @initgrow = @potsize
	END

	INSERT INTO @tblvlf
	SELECT @dbname, @logsize, @potsize, @count, 
		CASE WHEN @potsize <= 64 THEN (@potsize/(@potsize/@n_init_iter))*4
			WHEN @potsize > 64 AND @potsize < 1024 THEN (@potsize/(@potsize/@n_init_iter))*8
			WHEN @potsize >= 1024 THEN (@potsize/(@potsize/@n_init_iter))*16
			END, 
		@n_init_iter, @initgrow, CASE WHEN (@potsize/@n_iter_final) <= 1024 THEN (@potsize/@n_iter_final) ELSE 1024 END
	
	SET @i = 0
	WHILE @i <= @n_init_iter
	BEGIN
		IF @i = 1
		BEGIN
			--Log Autogrow should not be above 1GB
			PRINT CHAR(13) + '-- Now for the log file growth:'
			PRINT 'ALTER DATABASE [' + @dbname + '] MODIFY FILE ( NAME = N''' + @filename + ''', SIZE = ' + CONVERT(VARCHAR, @initgrow) + 'MB , FILEGROWTH = ' + CASE WHEN (@potsize/@n_iter_final) <= 1024 THEN CONVERT(VARCHAR, (@potsize/@n_iter_final)) ELSE '1024' END + 'MB );'
		END
		IF @i > 1
		BEGIN
			PRINT 'ALTER DATABASE [' + @dbname + '] MODIFY FILE ( NAME = N''' + @filename + ''', SIZE = ' + CONVERT(VARCHAR, @initgrow*@i)+ 'MB );'
		END		
		SET @i = @i + 1
		CONTINUE
	END
	FETCH NEXT FROM cshrk INTO @dbname, @count
END
CLOSE cshrk
DEALLOCATE cshrk;

DROP TABLE #loginfo;

SELECT dbname AS [Database_Name], Actual_log_size_MB, Potential_log_size_MB, Actual_VLFs, 
	Potential_VLFs, Growth_iterations, Log_Initial_size_MB, File_autogrow_MB
FROM @tblvlf;
GO

事务日志的循环性质

事务日志是一种回绕的文件。 例如，假设有一个数据库，它包含一个分成四个 VLF 的物理日志文件。 当创建数据库时，逻辑日志文件从物理日志文件的始端开始。 新日志记录被添加到逻辑日志的末端，然后向物理日志的末端扩张。 日志截断将释放全部记录在最小恢复日志序列号 (MinLSN) 之前出现的所有虚拟日志。 MinLSN 是成功进行数据库范围内回滚所需的最早日志记录的日志序列号。 示例数据库中的事务日志的外观与下图所示相似。

Illustrates how a physical log file is divided into virtual logs

当逻辑日志的末端到达物理日志文件的末端时，新的日志记录将回绕到物理日志文件的始端。

Illustrates how a logical transaction log wraps around in its physical log file

这个循环不断重复，只要逻辑日志的末端不到达逻辑日志的始端。 如果经常截断旧的日志记录，始终为到下一个检查点前创建的所有新日志记录保留足够的空间，则日志永远不会填满。 但是，如果逻辑日志的末端真的到达了逻辑日志的始端，将发生以下两种情况之一：

• 如果对日志启用了 FILEGROWTH 设置且磁盘上有可用空间，则文件就按 growth_increment
  参数指定的数量增大，并且新的日志记录将添加到增大的空间中 。 有关设置的详细信息 FILEGROWTH ，请参阅 ALTER
  DATABASE 文件和文件组选项 (Transact-SQL) 。
• 如果未启用 FILEGROWTH 设置，或保存日志文件的磁盘的可用空间比 growth_increment 中指定的数量少，则会出现
  9002 错误 。 请参考解决事务日志已满的问题，了解详细信息。

如果日志包含多个物理日志文件，则逻辑日志在回绕到首个物理日志文件始端之前，将沿着所有物理日志文件移动。

重要

有关事务日志大小管理的详细信息，请参阅管理事务日志文件的大小。

日志截断

日志截断主要用于阻止日志填充。 日志截断从 SQL Server 数据库的逻辑事务日志中删除不活动的虚拟日志文件，释放逻辑日志中的空间以便物理事务日志重用这些空间。 如果事务日志从不截断，它最终将填满分配给物理日志文件的所有磁盘空间。 但是，在截断日志前，必须执行检查点操作。 检查点将当前内存中已修改的页（称为“脏页”）和事务日志信息从内存写入磁盘。 执行检查点时，事务日志的不活动部分将标记为可重用。 此后，日志截断可以释放不活动的部分。 有关检查点的详细信息，请参阅 数据库检查点 (SQL Server) 。

下列各图显示了截断前后的事务日志。 第一个图显示了从未截断的事务日志。 当前，逻辑日志使用四个虚拟日志文件。 逻辑日志开始于第一个逻辑日志文件的前面，并结束于虚拟日志 4。 MinLSN 记录位于虚拟日志 3 中。 虚拟日志 1 和虚拟日志 2 仅包含不活动的日志记录。 这些记录可以截断。 虚拟日志 5 仍未使用，不属于当前逻辑日志。

Illustrates how a transaction log appears before it is truncated

第二个图显示了日志截断后的情形。 已释放虚拟日志 1 和虚拟日志 2 以供重新使用。 现在，逻辑日志开始于虚拟日志 3 的开头。 虚拟日志 5 仍未使用，它不属于当前逻辑日志。

Illustrates how a transaction log appears after it is truncated

除非由于某些原因导致延迟，否则将在以下事件后自动发生日志截断：

• 简单恢复模式下，在检查点之后发生。
• 完整恢复模式或大容量日志恢复模式下，在日志备份之后发生（如果自上次备份后出现检查点）。

日志截断会由于多种因素发生延迟。 如果日志截断延迟的时间较长，则事务日志可能会填满磁盘空间。 有关信息，请参阅可能导致日志截断和排查完整事务日志 (SQL Server 错误 9002) 的因素。

事务日志备份

本节介绍了有关如何备份和还原（应用）事务日志的概念。 在完整恢复模式和批量日志恢复模式下，执行例行事务日志备份（“日志备份” ）对于恢复数据十分必要。 可以在任何完整备份运行的时候备份日志。 有关恢复模型的详细信息，请参阅 SQL Server 数据库的备份和还原。

在创建第一个日志备份之前，必须先创建完整备份（如数据库备份或一组文件备份中的第一个备份）。 仅使用文件备份还原数据库会较复杂。 因此，建议您尽可能从完整数据库备份开始。 此后，必须定期备份事务日志。 这不仅能最小化工作丢失风险，还有助于事务日志的截断。 通常，事务日志在每次常规日志备份之后截断。

重要

建议经常进行日志备份，其频率应足够支持业务需求，尤其是对损坏的日志存储可能导致的数据丢失的容忍程度。
适当的日志备份频率取决于您对工作丢失风险的容忍程度与所能存储、管理和潜在还原的日志备份数量之间的平衡。 实现恢复策略时，请考虑必需的 RTO
和 RPO，特别是日志备份频率。 每 15 到 30 分钟进行一次日志备份可能就已足够。
但是如果您的业务要求将工作丢失的风险最小化，请考虑进行更频繁的日志备份。 频繁的日志备份还有增加日志截断频率的优点，其结果是日志文件较小。

重要

若要限制需要还原的日志备份的数量，必须定期备份数据。 例如，可以制定这样一个计划：每周进行一次完整数据库备份，每天进行若干次差异数据库备份。
同样，实现恢复策略时，请考虑所需 RTO 和 RPO，尤其是完整和差异的数据库备份频率。

有关事务日志备份的详细信息，请参阅 事务日志备份 (SQL Server) 。

事务日志支持的操作

事务日志支持以下操作：

恢复个别的事务。

如果应用程序发出 ROLLBACK 语句，或者数据库引擎检测到错误（例如失去与客户端的通信），使用日志记录回退未完成的事务所做的修改。

启动事务时恢复所有未完成SQL Server事务。

当服务器发生故障时，数据库可能处于这样的状态：还没有将某些修改从缓存写入数据文件，在数据文件内有未完成的事务所做的修改。 启动 SQL Server 实例时，它将对每个数据库执行恢复操作。 前滚日志中记录的、可能尚未写入数据文件的每个修改。 在事务日志中找到的每个未完成的事务都将回滚，以确保数据库的完整性。 有关详细信息，请参阅还原和恢复概述 (SQL Server)。

将还原的数据库、文件、文件组或页前滚至故障点。

在硬件丢失或磁盘故障影响到数据库文件后，可以将数据库还原到故障点。 先还原上次完整数据库备份和上次差异数据库备份，然后将后续的事务日志备份序列还原到故障点。

还原每个日志备份时，数据库引擎将重新应用日志中记录的所有修改，前滚所有事务。 最后的日志备份还原后，数据库引擎将使用日志信息回退到该点上未完成的所有事务。 有关详细信息，请参阅还原和恢复概述 (SQL Server)。

支持事务复制。

日志读取器代理程序监视已为事务复制配置的每个数据库的事务日志，并将已设复制标记的事务从事务日志复制到分发数据库中。 有关详细信息，请参阅 事务复制的工作原理。

支持高可用性和灾难恢复解决方案：Always On 可用性组、数据库镜像和日志交付。

备用服务器解决方案（Always On 可用性组数据库镜像和日志交付）严重依赖于事务日志。

在 Always On 可用性组方案中，对数据库（主副本）的每次更新都会立即在数据库（辅助副本）的单独完整副本中重现。 主要副本直接将每个日志记录发送到次要副本，这可将传入日志记录应用到可用性组数据库，并不断前滚。 有关详细信息，请参阅 AlwaysOn 故障转移群集实例

在日志传送方案中，主服务器将主数据库的活动事务日志发送到一个或多个目标服务器。 每个辅助服务器将该日志还原为其本地的辅助数据库。 有关详细信息，请参阅 关于日志传送。

在数据库镜像方案中，数据库（主体数据库）的每次更新都在独立的、完整的数据库（镜像数据库）副本中立即重新生成。 主体服务器实例立即将每个日志记录发送到镜像服务器实例，镜像服务器实例将传入的日志记录应用于镜像数据库，从而将其继续前滚。 有关详细信息，请参阅 数据库镜像。

事务日志特征

事务日志SQL Server 数据库引擎特征：

• 事务日志是作为数据库中的单独的文件或一组文件实现的。 日志缓存与数据页的缓冲区缓存分开管理，从而在数据页中生成简单、快速且可靠的SQL
  Server 数据库引擎。 有关详细信息，请参阅事务日志物理体系结构。
• 日志记录和页的格式不必遵守数据页的格式。
• 事务日志可以在几个文件上实现。 通过设置日志的 FILEGROWTH 值可以将这些文件定义为自动扩展。
  这样可减少事务日志内空间不足的可能性，同时减少管理开销。 有关详细信息，请参阅 ALTER DATABASE (Transact-SQL)
  文件和文件组选项。
• 重用日志文件中空间的机制速度快且对事务吞吐量影响最小。
• 有关事务日志体系结构和内部组件的详细信息，请参阅 SQL Server 事务日志体系结构和管理指南。

事务日志截断

日志截断将释放日志文件的空间，以便由事务日志重新使用。 必须定期截断事务日志，防止占满分配的空间。 几个因素可能延迟日志截断，因此监视日志大小很重要。 某些操作可以最小日志量进行记录以减少其对事务日志大小的影响。

日志截断从 SQL Server 数据库的逻辑事务日志中删除非活动虚拟日志文件 (VLF) ，从而释放逻辑日志中供物理事务日志重用的空间。 如果事务日志从不截断，它最终将填满分配给物理日志文件的所有磁盘空间。

为了避免空间不足，除非由于某些原因延迟日志截断，否则将在以下事件后自动进行截断：

• 简单恢复模式下，在检查点之后发生。
• 在完整恢复模式或大容量日志恢复模式下，如果自上一次备份后生成检查点，则在日志备份后进行截断（除非是仅复制日志备份）。
  有关详细信息，请参阅本主题后面的可能延迟日志截断的因素。

备注

日志截断并不减小物理日志文件的大小。 若要减少物理日志文件的物理大小，则必须收缩日志文件。 有关收缩物理日志文件大小的信息，请参阅
管理事务日志文件的大小。 但是，请记住可能延迟日志截断的因素。
如果在日志收缩后还需要存储空间，则会再次增加事务日志，导致在增加日志操作期间产生性能开销。

可能延迟日志截断的因素

在日志记录长时间处于活动状态时，事务日志截断将延迟，事务日志可能填满，这一点我们在本主题（很长）前面提到过。

重要

若要了解如何响应完整的事务日志，请参阅排查"事务日志已满 (SQL Server错误 9002 ) 。

实际上，日志截断会由于多种原因发生延迟。 查询 sys.databases 目录视图的 log_reuse_wait 和 log_reuse_wait_desc 列，了解哪些因素（如果存在）阻止日志截断。 下表对这些列的值进行了说明。

可以尽量减少日志量的操作

最小日志记录是指只记录在不支持时间点恢复的情况下恢复事务所需的信息。 本主题介绍在大容量日志 恢复模式 下（以及简单恢复模式下）按最小方式记录、但在运行备份时例外的操作。

备注

内存优化表不支持最小日志记录。

备注

在完整 恢复模式下，所有大容量操作都将被完整地记录下来。
但是，可以通过将数据库暂时切换到用于大容量操作的大容量日志恢复模式，最小化一组大容量操作的日志记录。
最小日志记录比完整日志记录更为有效，并在大容量事务期间，降低了大规模大容量操作填满可用的事务日志空间的可能性。
不过，如果在最小日志记录生效时数据库损坏或丢失，则无法将数据库恢复到故障点。

下列操作在完整恢复模式下执行完整日志记录，而在简单和大容量日志恢复模式下按最小方式记录日志：

• 批量导入操作（bcp、BULK INSERT 和 INSERT…SELECT）。
  有关在何时对大容量导入表按最小方式进行记录的详细信息，请参阅 Prerequisites for Minimal Logging in
  Bulk Import。

启用事务复制时，将完全记录 BULK INSERT 操作，即使处于大容量日志恢复模式下。

• SELECT INTO 操作。

启用事务复制时，将完全记录 SELECT INTO 操作，即使处于大容量日志恢复模式下。

• 对大值数据类型进行部分更新，在 .WRITE 插入或追加新数据时，使用 .WRITE 语句中的 子句。
  注意，在更新现有值时没有使用最小日志记录。 有关大值数据类型的信息，请参阅 Transact- (数据类型SQL) 。
• 在“text”、“ntext”和“image”数据类型列中插入或追加新数据时的 WRITETEXT 和 UPDATETEXT 语句。
  注意，在更新现有值时没有使用最小日志记录。

警告

UPDATETEXT和 WRITETEXT 语句WRITETEXT;请避免在新的应用程序中使用它们。

• 如果数据库设置为简单或大容量日志恢复模式，则无论是脱机还是联机执行操作，都会按最小方式记录一些索引 DDL 操作。
  按最小方式记录的索引操作如下：

CREATE INDEX 操作（包括索引视图）。

ALTER INDEX REBUILD 或 DBCC DBREINDEX 操作。

警告

语句DBCC DBREINDEXDBCC DBREINDEX;请勿在新的应用程序中使用它。

备注

索引生成操作使用最小日志记录，但在并发执行备份时可能会延迟。 此延迟由使用简单或大容量日志恢复模式时的最小日志记录缓冲池页的同步要求引起。

DROP INDEX 新堆重新生成（如果适用）。 始终完整记录操作期间 DROP INDEX 索引 DROP INDEX 释放。