sql server 把事务日志文件划分为多个vlf（virtual log file），即虚拟日志文件。
一、vlf的4种状态
1、active。表示vlf中存在活动的事务（即未完成的事务）。
2、recoverable。表示vlf中的事务全部已经完成，但是某些操作（例如数据库镜像、复制等）还需要用到这些数据，因此不可以被覆盖。
3、reusable。表示vlf中的数据已经不需要了，可以被覆盖。
4、unused。表示vlf从未被使用。
二、简单恢复模式时的vlf
1、文件的初始状态
测试数据库名为db01，网站空间，恢复模式设为简单。mdf与ldf文件初始状态如下：
2、填充数据
运行以下脚本，填充大量数据：
create table testtable (userid varchar(100),pwd varchar(200))
declare @i int
declare @d1 datetime
set @i=0
-- truncate table [testtable]
insert into testtable select 'a','b'
while @ibegin
set @d1 = getdate()
insert into [testtable]
select userid+cast(floor(rand()*1000) as nvarchar(50)),
cast(floor(rand()*100000) as nvarchar(50))+userid+cast(floor(rand()*100) as nvarchar(50))
from [testtable]
set @i=@i+1
-- print '第' + cast(@i as varchar(50)) + '圈，用时：' + cast(datediff(ms,@d1,getdate()) as varchar(50)) + 'ms'
end
3、检查磁盘增长
运行脚本后，ldf文件由最初的1mb增长到53mb。
打开“报表”－－“标准报表”－－“磁盘使用情况”。
4、检查vlf
执行dbcc loginfo，检查vlf使用情况。
上表中的fileid=2表示数据库的第2个文件，对于db01这个测试库，第2个文件就是唯一的ldf文件。filesize表示vlf的大小。fseqno表示vlf的序列号，如果为零则表示这个vlf未被使用。createlsn为零，表示创建数据库时就同时创建了这些vlf；不为零则表示在lsn产生时才创建这个vlf。
status列表示vlf的状态，如果为0则表示这个vlf为reusable或者unused，如果为2则表示这个vlf为active或者recoverable。
可以通过sys.databases 系统视图查看事务日志不能被截断的原因。例如：
select log_reuse_wait , log_reuse_wait_desc from sys.databases where
5、查看vlf中的lsn
通过dbcc loginfo，可以查到status=2的最小的fseqno=1865，服务器空间，那么可以通过以下语句查询这个vlf中的lsn。
select * from ::fn_dblog(1865000000000000001 ,1866000000000000000 )
上图第1列显示的16进制lsn不能直接使用，需要换算成正常的10进制lsn。以第一行为例，第1段为vlf序列号，749h换算成10进制为1865；第2段为日志区块的第1个扇区编号，6eh换算成10个字符的10进制为0000000110；第3段为该扇区内的流水号，1h换算成4个字符的10进制为0001。因此，正常的lsn为186500000001100001。
6、checkpoint的影响
从上表可以看出，存在一些status=2的vlf。如果数据缓冲区仍有一些“脏数据”未回写到mdf文件，可执行checkpoint，使“脏数据”立即进行回写。（前面的例子中，从sys.databases 系统视图可以看到有一个事务还未完成，导致checkpoint延迟）
说明：在生产环境中，美国服务器，即使在简单恢复模式，如果有个事务很早就begin transaction，而忘记被commit/rollback，结果可能看到一个很大的日志文件。
再执行dbcc loginfo，检查vlf使用情况。可见绝大多数的vlf已经status=0。
再检查“磁盘使用情况”。
7、收缩日志文件
在简单恢复模式时，自动或手动checkpoint操作，以及对数据库做完全备份都会将recoverable状态的vlf标记成status=0，这些vlf可以被不断重用。如果没有大量的事务导致checkpoint延迟或者“脏数据”回写延迟，ldf文件就不需要增长。
收缩日志文件时，数据库引擎会检查vlf的状态，将status=0（即reusable和unused）的vlf所占用的空间释放出来，然后ldf再收缩它的边界。
三、完整恢复模式时的vlf
在完整恢复模式时，事务日志需要单独备份，因此checkpoint会被延迟，导致recoverable状态的vlf不能被标记成reusable状态。由于这些vlf不能被重用，导致ldf文件持续增长。
执行事务日志备份后，vlf的状态被标记为status=0，才可以被重用。
为了避免ldf过度增长，应当增加事务日志备份的频率。
四、参考资料
1、《事务日志 (sql server)》
2、《数据库检查点 (sql server)》
3、《简介数据库日志文件的增长》
本文出自 “jimshu (mcitp/mcse/mct)” 博客，请务必保留此出处