日志文件持续增长的案例

在完整恢复模式时，只有执行事务日志备份之后日志才被截断。 在日志被截断之前，有一种特殊情形导致数据文件的空间几乎没有变化，而日志文件却持续增长。一、搭

　　在完整恢复模式时，只有执行事务日志备份之后日志才被截断。

　　在日志被截断之前，有一种特殊情形导致数据文件的空间几乎没有变化，而日志文件却持续增长。

一、搭建测试环境

1. 创建数据库

CREATE DATABASE [db01]

ON PRIMARY

( NAME = N'db01', FILENAME = N'C:\sqldata\db01.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'db01_log', FILENAME = N'C:\sqldata\db01_log.ldf' , SIZE = 1024KB , FILEGROWTH = 1024KB)

ALTER DATABASE [db01] SET RECOVERY FULL

2. 创建表并插入一条新记录

USE db01

CREATE TABLE table1

(UserID int,pwd char(20),OtherInfo char(4000),modifydate datetime)

INSERT table1

VALUES ( 123,'456','this is the first record',getdate() )

3. 再插入999条记录

DECLARE @i int

SET @i=0

WHILE @i<999

BEGIN

INSERT INTO table1

SELECT cast(floor(rand()*100000) as int), cast(floor(rand()*100000) as varchar(20)), cast(floor(rand()*100000) as char(4000)), GETDATE()

SET @i=@i+1

END

4. 查看数据库的磁盘使用空间

　　此时，mdf文件为7MB，ldf文件为1MB。使用情况如下图。

二、第一次测试

1. 重复修改第一条记录

DECLARE @i int

SET @i=0

WHILE @i<1000

BEGIN

UPDATE table1

SET pwd = cast(floor(rand()*100000) as varchar(20)),

OtherInfo=cast(floor(rand()*100000) as char(4000)),

modifydate = GETDATE()

WHERE UserID=123

SET @i=@i+1

END

2. 查看数据库的磁盘空间

　　查看数据库的磁盘空间，结果发现：mdf文件和ldf文件的磁盘空间都几乎没有变化。

3、原因分析

　　按照我们的理解：在完整恢复模式时，事务日志应当记录table1的所有数据变化的LSN记录，然后可以通过重做LSN从而恢复到任意时间点。

　　既然我们已经对table1的第1条记录反复修改了1000次，理论上应当在ldf文件中记录这些修改的LSN记录。可是上面的测试结果显示ldf文件的磁盘空间没有变化，说明ldf文件中没有保存所有的LSN记录。这是为什么呢？

　　回到事务日志的工作机制上，事务日志备份的恢复目标是什么呢？首先需要基于一个完整备份，然后从这个完整备份的最后一个LSN开始依次重做所有的LSN记录，从而将数据恢复直到最后一笔交易。

　　要注意到，数据库db01自创建以来，没有做过完整备份。原因终于找到了！由于没有完整备份，数据库引擎认为ldf没有必要保存这些LSN记录（VLF被自动复用），因为这些LSN记录不能用于恢复。

三、第2次测试

1. 完整备份

BACKUP DATABASE [db01]

TO DISK = N'C:\Backup\db01.bak' WITH FORMAT, INIT

2. 重复修改第1条记录

　　再重复修改第1条记录，反复修改1000次。

3. 查看磁盘空间

　　查看数据库的磁盘空间，结果发现：mdf文件仍然为7MB，而ldf文件从1MB增长到18MB。当然，如果反复修改的次数更多，将会使ldf增长更多。

4. 原因分析

　　如果前面没有做过事务日志备份，则ldf文件中保存着自上一次完整备份以来的所有LSN记录；如果前面有做过事务日志备份，则ldf文件中保存着自上一次事务日志备份以来的所有LSN记录。

　　mdf文件没有增长，是因为数据变化始终发生在第一个数据行，，始终用新的数据去覆盖旧的数据，因此不需要新的磁盘空间。而ldf文件持续增长，是因为需要新的磁盘空间用来保存所有的LSN记录。

四、场景示例

　　例如，某个table记录了用户最后登入某个应用系统的时间。当无数个用户频繁登入系统，即使这些用户登入应用系统后没有任何操作就退出了系统，最后导致mdf文件几乎不变而ldf文件却持续增长。

五、收缩日志文件

　　请参考 《如何收缩事务日志》

本文出自 “我们一起追过的MSSQL” 博客，请务必保留此出处