在完整恢复模式时，只有执行事务日志备份之后日志才被截断。 在日志被截断之前，有一种特殊情形导致数据文件的空间几乎没有变化，而日志文件却持续增长。一、搭
在完整恢复模式时，只有执行事务日志备份之后日志才被截断。
在日志被截断之前，有一种特殊情形导致数据文件的空间几乎没有变化，而日志文件却持续增长。
一、搭建测试环境
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
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
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” 博客，请务必保留此出处
