管理 SQL Server 的事务日志

2019-11-03 08:35:02

字体：大中小

来源：转载

供稿：网友

在 SQL Server 实例中的每一个数据库，都有一个日志，它记录着数据库的所有更改。由于这个日志是独立的，在更改发生之前，事务日志允许在硬件故障或应用程序错误时，对数据库回滚或保存事务。由于它的角色的重要性，事务日志被保存在一个或多个与数据库文件独立的日志文件中；日志记录是在内容的变更从缓存写到数据库文件中以前发生的。

对每个数据库，事务日志支持以下操作：
当发出一个回滚操作或数据库引擎检测到一个错时，进行事务回滚；
当服务器失改时，进行一个完整的事务回滚。这个事务在SQL Server 重启时进行回滚。
当服务器失败时，将未完成的事务写入到日志文件，而不是数据文件中。当 SQL Server 重启时，这些未完成的事务将会写入数据文件。
当发生硬件错误时，对恢复的数据库、文件组、文件或页向前滚动到失败点。事务将滚动到最后一个完整备份或差异备点。
对事务复制、数据库镜像、日志传输提供支持。

这些记录事务日志的文件，会由数据库引擎根据物理文件的实际情况，自动地分解为多个虚拟文件。数据库引擎也会判断在何时对哪些虚拟文件进行截断。你可以指定物理日志文件的最小值和最大值，并可以配置扩展文件时的增长率。另外，你可以向日志增加物理文件、删除文件、增加日志的大小或收缩日志。

在这篇文章中，我将解释如何执行这些任务，以开始管理你的事务日志，同时，我提供了一些例子以演示每一个任务如何工作。在这些例子中，我使用位于本地 SQL Server 2008 实例上的 EmplyeeDb 数据库：

USE master;

IF EXISTS
(
  SELECT name FROM sys.databases
  WHERE name = 'EmployeeDB'
)
DROP DATABASE EmployeeDB;

CREATE DATABASE EmployeeDB
ON
(
  NAME = EmployeeDB_dat,
  FILENAME = 'C:/SqlData/EmployeeDb.mdf'
)
LOG ON
(
  NAME = EmployeeDB_log,
  FILENAME = 'C:/SqlData/EmployeeDb.ldf'
);

注意：我是在一个指定的位置上创建了一个数据库文件，而不是 SQL Server 默认的位置。如果运行这段代码，可以将数据库定位到你想指定的位置。创建数据库以后，我可以使用 SELECT...INTO 语句从AdventureWorks2008 数据库创建表，并传输数据。

USE EmployeeDB;

IF OBJECT_ID('Employees', 'U') IS NOT NULL
DROP TABLE dbo.Employees;

SELECT BusinessEntityID,
  FirstName,
  LastName,
  JobTitle,
  PhoneNumber,
  EmailAddress,
  AddressLine1,
  AddressLine2,
  City,
  StatePRovinceName,
  PostalCode,
  CountryRegionName
INTO dbo.Employees
FROM AdventureWorks2008.HumanResources.vEmployee;

你不一定非要在这个数据库中执行这段代码，但是它作为一个小小的测验，有助于你学习事务日志的有关内容。如果你计划使用另外的数据库，只需要替换一下例子代码中的数据库名称即可。

配置恢复模式

每个 SQL Server 数据库都有一个恢复模式属性，(the Recovery Model), 它指示事务日志如何记录，如：事务日志是否可以被备份，以及恢复操作的许可类型。默认情况下，一个新的数据库从 Model 数据库继承了一个恢复模式。当然，你也可以修改默认设置为其它模式。

你可以配置一个 SQL Server 数据库的恢复模式为以下几种之一：
简单模式(Simple): 在这种模式下，事务日志的备份是不安全的，这意味着你不能对备份之后的事务日志进行管理。这种模式也会自动的扩展日志空间，所以几乎不需要去管理事务日志的空间。然而，这种模式也是风险最大的一种模式，数据库只能被恢复到最后一次备份的时间点，而在最后一次备份之后执行的事务将会丢失。这种模式通常用于系统数据库、或者用于测试和开发阶段。或者是几乎仅有只读情况的数据仓库数据库。这种情况下，一些操作只是尽可能少的被记录。
完整模式(Full): 由于这种模式可以提示指定时间点的恢复，因此它可以备份并且也应当进行备份。这种模式比简单模式的风险要小。但是，在完整模式下，所有的操作都被完整的记录，包括大数据量操作。这种模式适用于生产环境。
大数据量记录模式(Bulk Logged): 这种模式可以看作是完整模式的补充，因为在这种模式下，大数量操作只是被最小化的记录。例如，你可能要大量的加载数据但你不希望这些事务日志被记录，因为你只是希望加载数据而已。在这种情况下，你可以在导入数据时，将模式由完整模式切换到大数据量模式，执行完后，再恢复到完整模式。（需要注意的是：在切换回完整模式后，你应当做一次完整备份）
你可以在数据库上通过执行 ALTER DATABASE 语句，和指定 Set Recovery 来切换这些模式，例子如下：

USE master;

ALTER DATABASE EmployeeDB
SET RECOVERY FULL;

在上面的代码中，我修改了 EmployeeDB 数据库，并将恢复模式设置为完整模式 FULL。注意：由于默认的 model 数据库是被配置为完整模式 Full ，这也意味着 EmployeeDB 数据库被自动配置为完整模式，因为它是继承自 model 数据库的。所以，如果在你的服务器上， model 数据库的默认设置没有被更改的话，上面的的例子中 ALTER DATABASE 并不会改变什么，但是你要注意，当你将数据从简单模式切换到完整模式时，有时候必须执行一些其它步骤，例如进行一个完整备份。在SQL Server 在线教程中主题 "Considerations for Switching from the Simple Recovery Model" 描述了将数据库的恢复模式从简单模式转换为完整模式或大容量模式时，有哪些步骤要执行。
你也可以在 SQL Server Management Studio中设置恢复模式。在对象浏览器中右键单击数据库名称，并选择“属性”，在数据库属性对话框中，单击选项页，并设置恢复模式属性。

监控日志文件
在维护数据库的事务日志过程中，你可能需要经常获取日志的一些信息以便检查其设置或者已经使用了多少日志空间。一种方法是使用 sys.database_files 分类视图，这个视图返回关于数据库文件的详细信息，包括：文件类型、当前文件大小、以及文件增长设置。
在下面的例子中，我使用 sys.database_files 来获取 EmployeeDB 数据库的日志文件的数据：
USE EmployeeDB;

SELECT name,
  size, -- in 8-KB pages
  max_size, -- in 8-KB pages
  growth,
  is_percent_growth
FROM sys.database_files
WHERE type_desc = 'LOG'
这条语句返回了当前的文件大小（按8-KB的页面大小），文件可增长到的最大大小 (同样按 8_KB 的页面大小)，增长率和是否按百分比增长标记。该标记指示数据库文件的大小按何种方式增长。如果标记被设置为0，那么增长率就是 8-KB ，如果设置为 1，则按百分比增长。
上述代码返回的结果大致如下：
Name            size  max_size  growth  is_percent_growth
EmployeeDB_log  128   268435456 10      1
如结果中所示，这条语句只返回一行记录。这是因为 EmployeeDB 只配置了一个日志文件。上面的结果还反映了 EmployeeDB_log 的当前文件大小是 128 个8-KB 的页面大小，它可以增长到 268,435,456 个8-KB 的页面大小，增长率是按 10%的速率。
你还可以使用 DBCC SQLPERF 语句返回一个 SQL Server 实例的每个数据库的事务日志信息，要获取日志数据，你必须在参数中使用 LOGSPACE 关键字，如下所示：
DBCC SQLPERF(LOGSPACE);
这条语句返回以 MB 计算的日志大小，日志空间使用的百分比，以及你的 SQL Server 实例中每个数据库的日志状态。 EmployeeDB 数据库的信息如下：
Database Name  Log Size (MB)  Log Space Used (%)  Status
EmployeeDB     0.9921875      40.05906            0
这个例子中 EmployeeDB 日志大约是 1 MB 大小，并且使用了 40% 的日志空间。
你也可以在 SQL Server Management Studio 中生成一个图形化的报表，结果类似于执行 DBCC SQLPERF 语句。方法是：在对象浏览器中，右键单击数据库名称，选择报表，再选择标准报表，最后点击磁盘利用率。

备份日志文件
如果你将数据库的恢复模式配置为完全模式或大容量模式，你就应当有规律的备份事务日志，这样你就可以截断日志并释放不活动的日志空间。备份也可以用于恢复数据库（通常与数据库备份一起使用）。
在事务日志备份之前，必须先执行过一个数据库的完整备份。通常，在我使用本文中的日志备份前，一般都先执行下面的数据库备份语句：
BACKUP DATABASE EmployeeDB
TO DISK = 'E:/DbBackup/EmployeeDB_dat.bak';
注意：执行这段代码时，确认指定路径存在或指定一个另外的路径。
执行完数据库备份后，我一般运行下面的数据修改语句，以使当前日志不包含已备份的内容：

USE EmployeeDB;

UPDATE Employees
SET JobTitle = 'To be determined';

UPDATE Employees
SET CountryRegionName = 'US'
WHERE CountryRegionName = 'United States';

DELETE Employees
WHERE BusinessEntityID > 5;
然后我再运行 DBCC SQLPERF 查看日志空间的统计信息，该语句返回下面的结果：
Database Name  Log Size (MB)  Log Space Used (%)  Status
EmployeeDB     0.9921875      64.41929            0
你可以看到，日志空间的使用率已从40%提升到接近65%。
备份完数据库，你就可以备份事务日志了。执行事务日志的备份，使用 BACKUP LOG 语句，并指定备份位置，如下：
-- back up transaction log
BACKUP LOG EmployeeDB
TO DISK = 'E:/LogBackup/EmployeeDB_log.bak';
同样要注意路径的问题。
这里我指定了备份路径，然而， BACKUP 还支持其它选项，可以在SQL Server Books Online查看 “BACKUP (Transact-SQL)” 主题以获得更多信息。
执行完事务日志的备份以后， SQL Server 数据库引擎会自动截断不活动的日志空间。（注意：截断事务日志只是移除了不活动的虚拟日志空间，并不减小文件大小）要减小日志文件，你应当对文件进行收缩。要检查是否截断了日志，请再次运行 DBCC SQLPERF 语句。现在的结果应当如下面所示：
Database Name  Log Size (MB)  Log Space Used (%)  Status
   EmployeeDB     0.9921875      44.88189            0
现在日志空间已下降到45%。

修改日志文件
你可以使用 ALTER DATABASE 语句来修改日志文件。在执行语句时，必须使用适当的选项指定修改文件的原因（ MODIFY FILE clause）。除了给日志文件指定适当的逻辑名外，还有以下三个参数可用：
SIZE: 为日志文件指定大小。你可以指定以 KB, MB, GB, 或 TB为单位的文件大小，例如： 10 MB 或 1 GB。如果在添加文件时没有指定文件大小，数据库引擎使用默认的大小：1MB。新的文件大小必须比现在的大，否则在运行语句时会报错。
MAXSIZE: 这个参数指定该文件最大可以是多大。同样，你可以以 KB, MB, GB, 或 TB 为单位指定。如果你没有指定最大的文件大小，文件会一直变大，直到占满整个磁盘空间。
FILEGROWTH: 增量用于文件扩展时。可以KB, MB, GB, 或 TB 指定，或者用百分比。如 10%。如果没有指定增量的单位，则默认为MB，如果没有指定增量，则默认为10%。如果指定增量为0，则不允许自动增加。
下面用 ALTER DATABASE 语句来修改 EmployeeDB 数据库的日志文件 EmployeeDB_log：
-- modify log file
ALTER DATABASE EmployeeDB
MODIFY FILE
(
    NAME = EmployeeDB_log,
    SIZE = 2MB,
    MAXSIZE = 200MB,
    FILEGROWTH = 10MB
);
如上所示，我在指定了日志文件的逻辑名称以后，设置了文件的大小 (2 MB)，最大值 (200 MB)，和增量 (10 MB)。
执行 ALTER DATABASE 语句后，可以查询 sys.database_files 的分类视图，以查看更改，结果如下：
Name             size  max_size  growth  is_percent_growth
EmployeeDB_log   256   25600     1280    0
现在文件的大小是 256 个 8-KB 页面，最大值 25,600 个 8-KB 增量是 1,280 个 8-KB 页面。

收缩日志文件
前面已经说过，要截断事务日志，你必须先备份日志，这样数据库引擎就可以自动的截断不活动的记录。然而截断日志并不会减小它的文件大小。要减小日志文件的大小，必须收缩日志文件，它会移除一个或多个不活动的虚拟日志文件。
要收缩日志文件，你可以使用指定了日志文件名称、目标大小（MB）的 DBCC SHRINKFILE 语句，下面的例子，使用DBCC SHRINKFILE 语句收综 EmployeeDB_log 文件：
-- shrink log file
DBCC SHRINKFILE(EmployeeDB_log, 1);
上面例子中文件的目标大小是 1 MB (128 8-KB pages)，执行该语句时，数据库引擎会将文件收缩到指定大小，但必须是在有足够的虚文件时。执行完后，可以通过查询 sys.database_files 分类视图，以检查是否文件已缩小，结果应当如下：
Name             size  max_size  growth  is_percent_growth
EmployeeDB_log     128    25600       1280       0
你可以看到，文件的大小已从 256 个 8-KB 减小到 128。如果数据库引擎无法释放空间，它会提示你一些建议的步骤，你可以按提示重新运行 DBCC SHRINKFILE 语句。

添加或删除日志文件
如果你在增加日志文件的大小，一种方法是为日志增加文件。
你可以在执行ALTER DATABASE语句时，使用 ADD LOG FILE 子句，在子句中，除了新的日志文件的逻辑名和物理名，你还可以指定以下参数：
SIZE: 日志文件的初始大小。你可以使用 KB, MB, GB, or TB为单位，如 10 MB 或 1 GB。如果不指定，则数据库引擎指定为默认值 1MB。
MAXSIZE: 日志文件的最大值，单位同上。如果不指定此参数，日志文件会一直增长，直到占满所在的磁盘空间。
FILEGROWTH: 增量，单位同上，也可以是百分比。如果指定了数字，但没有单位，则按 MB 。如果未指定此参数，则使用 10% 。如果指定为0，则不允许自动增长。
下面的例子添加 EmployeeDB_log2 到 EmployeeDB 的事务日志中。
ALTER DATABASE EmployeeDB
ADD LOG FILE
(
    NAME = EmployeeDB_log2,
    FILENAME = 'C:/SqlData/EmployeeDB2.ldf',
    SIZE = 2MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 10%
);
注意：我先是指定了逻辑名和物理名，随后定义了初始大小，最大值和增量。运行此语句以后，我可以通过查询 sys.database_files 分类视图来确认是否文件已被加入：
Name             size  max_size  growth  is_percent_growth
EmployeeDB_log   128   268435456 10      1
EmployeeDB_log2  256   6400      10      1
结果显示， EmployeeDB_log2 已被添加到数据库，其初始大小是：256个 8-KB 页面，最大值 6,400 个 8-KB 增量 10%.
你也可以使用 ALTER DATABASE 和子名 REMOVE FILE 来移除日志文件：
    ALTER DATABASE EmployeeDB
REMOVE FILE EmployeeDB_log2;
查询 sys.database_files 分类视图，返回的结果如下：

Name             size  max_size  growth  is_percent_growth
EmployeeDB_log   128   268435456 10      1
EmployeeDB_log2  1     6400      10      1
你可能注意到： EmployeeDB_log2 还在，但大小是 1 个 8-KB 页，这个物理文件已经被删除，但逻辑文件还在，你必须通过备份事务日志来移除它。备份以后，再查询  sys.database_files 分类视图，结果如下：
Name             size  max_size  growth  is_percent_growth
EmployeeDB_log   128   268435456 10      1
显示逻辑文件已移除。

总结
很明显，事务日志在 SQL Server 数据库中扮演着非常重要的角色，上面的内容指导您如何用它们来工作。上面关于事务日志，我没讲到的东西有：如何支持事务日志的复制、数据库的镜像和事务日志的发布，也没有讲到如何使用事务日志进行数据库的恢复。这些内容每个都是一个专题，但至少你现在对事务日志有了一个基本的认识，这是基础。不过，我还是强烈建议您认真阅读 SQL Server 在线帮助上关于事务日志的不同主题和其它资源，这样你就会对如何使用日志来工作，并最好的把握它。

上一篇：sql server视图的作用

下一篇：SQL Server为何走上虚拟化之路？