翻译（15w）

SQL Server中事务日志管理的阶梯，第1级：事务日志概览

Tony Davis，2013/10／30（第一次出版：2011／06／17）

该系列

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

当事情进行顺利时，没有必要特别注意事务日志是如何工作的，或者它是如何工作的。您只需要确信每个数据库都有正确的备份机制。当事情出错时，理解事务日志对于采取纠正措施是非常重要的，尤其是在需要及时更新数据库时！Tony Davis给出了每个DBA应该知道的正确级别。

级别1：事务日志概览

事务日志是一个文件，其中SQL Server将在日志文件关联的数据库上执行的所有事务和数据修改的记录存储在一起。如果发生导致SQL Server意外关闭的灾难，例如实例或硬件故障，则事务日志用于恢复数据库，其数据完整性很强。重新启动时，数据库进入一个恢复过程，在这个过程中，事务日志被读取，以确保所有有效的、提交的数据被写入到数据文件（向前滚动），并且撤消任何部分未提交事务的影响（回滚）。简而言之，事务日志是SQL Server确保数据库完整性和事务的酸性属性，尤其是持久性的基本手段。

DBA在管理事务日志方面的一些重要职责如下：

选择正确的恢复模式——SQL Server提供三种数据库恢复模式：完整（默认）、简单和大容量日志记录。DBA必须根据数据库的业务需求选择合适的模型，然后建立适合于该模式的维护过程。

执行事务日志备份——除非在简单模式下工作，否则DBA执行事务日志的常规备份是非常重要的。一旦在备份文件中捕获，日志记录随后可以应用于完整的数据库备份，以便执行数据库还原，从而重新创建数据库，如在以前的时间点上存在的数据库，例如，在故障之前。

监视和管理日志增长——在繁忙的数据库中，事务日志可以在规模上迅速增长。如果没有定期备份，或者如果大小不合适，或者分配了不正确的增长特性，事务日志文件就会填满，导致臭名昭著的“9002”（事务日志满）错误，这使SQL Server进入“只读”模式（如果在恢复期间发生），则进入“资源挂起”模式。

优化日志吞吐量和可用性——除了基本的维护（如备份）之外，DBA必须采取措施确保事务日志的充分性能。这包括硬件考虑，以及避免诸如日志碎片之类的情况，这种情况可能影响事务的性能。

在这个楼梯系列中，我们将详细地考虑这些核心维护任务中的每一个。在第一层中，我们将首先概述SQL Server如何使用事务日志，以及它影响DBA生命的两个最重要的方式，即数据库恢复和恢复，以及磁盘空间管理。

SQL Server如何使用事务日志

在SQL Server中，事务日志的物理文件，确定常规，虽然没有强制性，由扩展LDF。它是在创建数据库时自动创建的，以及主要的数据文件，通常由中密度纤维板扩展名标识，但也可以使用任何扩展来存储数据库对象和数据本身。事务日志，虽然通常作为单个物理文件实现，但也可以作为一组文件实现。然而，即使在后一种情况下，它仍然由SQL Server作为一个单一的顺序文件处理，因此，SQL Server不能并且不并行地写入多个日志文件，因此从将事务日志作为多个文件实现是没有性能优势的。在第7级中更详细地讨论了这个问题，即对事务日志进行大小调整和扩展。

每当T-SQL代码更改了数据库对象（DDL），或它所包含的数据不仅是数据或对象中的数据文件的更新，而且这些改变的细节记录在事务日志中的日志记录。每个日志记录都包含有关执行更改的事务的ID的详细信息，当事务启动和结束时，哪些页面被更改，所做的数据更改等等。

注意：事务日志不是审核跟踪。它不提供对数据库所做的更改的审计跟踪；它不保存对数据库执行的命令的记录，而是结果如何更改数据。

USE master ;

IF EXISTS ( SELECT  name

            FROM    sys.databases

            WHERE   name = 'TestDB' ) 

    DROP DATABASE TestDB ;

CREATE DATABASE TestDB ON

(

  NAME = TestDB_dat,

  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDB.mdf'

) LOG ON

(

  NAME = TestDB_log,

  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDB.ldf'

) ;

DBCC SQLPERF(LOGSPACE) ;

Database Name     Log Size (MB) Log Space Used (%) Status

---------------------------------------------------------

master            1.242188      34.27673           0

...<snip>...

TestDB            0.9921875     31.74213           0

清单1.1：为新库数据库初始日志文件大小

如您所见，日志文件的大小目前约为1 MB，大约为30%。

注意：实例上创建的用户数据库的初始大小和增长特性由模型数据库的属性决定，每个数据库将使用的默认恢复模型（在本例中为满）。我们将在第7级详细讨论这些属性的影响——度量和增长事务日志。

可以通过在磁盘上定位物理文件来确认文件的大小，如图1.1所示。

 

图1.1：数据和日志文件库

现在让我们对其中的数据文件进行备份，如清单1.2所示（你需要先创建“备份”目录）。请注意，此备份操作确保数据库真正在完全恢复模式下运行；在级别3中更详细地讨论了这一点——事务日志、备份和恢复

-- full backup of the database

BACKUP DATABASE TestDB

TO DISK ='C:\Backups\TestDB.bak'

WITH INIT;

GO

清单1.2：其中初始的完整备份

在数据或日志文件的大小没有变化，由于这种备份操作，或使用的日志空间的百分比，这也许是不足为奇的没有用户表或数据库中的数据，还。让我们把它的权利，并创建一个表，称为数据库logtest，填写100万行数据，并重新检查日志文件的大小，如清单1.3所示。这个脚本的作者，经常看到在sqlservercentral.com论坛，是Jeff Moden，这是他的一种许可转载。不要担心代码的细节，这里唯一重要的是我们插入了很多行。在您的计算机上执行此代码可能需要几秒钟，这并不是因为代码效率低下，而是幕后工作，写入数据和日志文件。

USE TestDB ;

GO

IF OBJECT_ID('dbo.LogTest', 'U') IS NOT NULL 

    DROP TABLE dbo.LogTest ;

--===== AUTHOR: Jeff Moden

--===== Create and populate 1,000,000 row test table.

-- "SomeID" has range of 1 to 1000000 unique numbers

-- "SomeInt" has range of 1 to 50000 non-unique numbers

-- "SomeLetters2";"AA"-"ZZ" non-unique 2-char strings

-- "SomeMoney"; 0.0000 to 99.9999 non-unique numbers

-- "SomeDate" ; >=01/01/2000 and <01/01/2010 non-unique

-- "SomeHex12"; 12 random hex characters (ie, 0-9,A-F)

SELECT TOP 1000000

        SomeID = IDENTITY( INT,1,1 ),

        SomeInt = ABS(CHECKSUM(NEWID())) % 50000 + 1 ,

        SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65)

        + CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65) ,

        SomeMoney = CAST(ABS(CHECKSUM(NEWID())) % 10000 / 100.0 AS MONEY) ,

        SomeDate = CAST(RAND(CHECKSUM(NEWID())) * 3653.0 + 36524.0 AS DATETIME) ,

        SomeHex12 = RIGHT(NEWID(), 12)

INTO    dbo.LogTest

FROM    sys.all_columns ac1

        CROSS JOIN sys.all_columns ac2 ;

DBCC SQLPERF(LOGSPACE) ;

清单1.3：插入一百万行到logtest表

请注意，日志文件大小已激增至近110 MB和日志91%（数字可能在你的系统略有不同）。如果我们要插入更多的数据，它将不得不再次增加大小以容纳更多的日志记录。同样，可以从物理文件中确认大小的增加（数据文件已增长到64 MB）。

通过重新运行清单1.2，我们可以在这一点上再次备份数据文件，它对日志文件的大小或文件中使用的空间百分比没有影响。现在，然而，让我们备份事务日志文件和检查的值，如清单1.4所示。

-- now backup the transaction log

BACKUP Log TestDB

TO DISK ='C:\Backups\TestDB_log.bak'

WITH INIT;

GO

DBCC SQLPERF(LOGSPACE) ;

Database Name   Log Size (MB) Log Space Used (%) Status

-------------------------------------------------------

master          1.242188      63.52201           0

...<snip>…

TestDB          99.74219      6.295527           0

清单1.4：备份事务日志库

日志文件仍然是相同的物理尺寸，但通过备份文件，SQL Server能够截断日志，可重用日志文件在“无效”超大型浮式结构物的空间；更多的日志记录可以增加而不需要身体的成长档案。而且，当然，我们获得的日志记录到一个备份文件，那么可以使用该文件作为数据库恢复过程的一部分，我们应该需要TESTDB数据库恢复到以前的状态。

总结

在第一级中，我们引入了事务日志，并解释了SQL Server如何通过写前日志机制来维护数据的一致性和完整性。我们还描述并简要演示了DBA如何将事务日志文件的内容捕获到备份文件中，然后将其作为恢复过程的一部分重用到数据库中。最后，我们强调了备份在控制事务日志大小方面的重要性。

在下一个层次，我们将仔细研究事务日志的体系结构。

资源:

tlogstairway_level1.sql

本文是SQL Server楼梯中事务日志管理的阶梯的一部分。

注册到我们的RSS提要并在我们发布一个新级别的楼梯时得到通知！

本文链接：

http://www.sqlservercentral.com/articles/Stairway+Series/73775/