sql server会在一下三种情况下认为你不需要维护日志备份：

  • 使用BACKUP LOG WITH NO_LOG 或者 BACKUP LOG WITH TRUNCATE_ONLY来进行日志备份
  • 简单恢复模式
  • 从来没有进行过完整备份

对于上述情况，数据库日志会在“足够满”的时候自动截断，以使VLFs重用。日志截断虽然是VLFs重用，但是不会收缩日志文件的大小。

---实验：验证日志的自动截断与日志收缩-----------------------------
--新建pubs数据库
USE pubs
--step1:查看pubs数据库中的VLFs信息,第一个VLFs的FSeqNo=23,Status=0,其他都为0
DBCC LOGINFO

--step2:查看pubs数据库是不是出于自动截断模式,如果last_log_backup_lsn为null则表示自动截断模式 
SELECT last_log_backup_lsn
FROM master.sys.database_recovery_status
WHERE database_id = db_id('pubs')
GO

--step3:创建表
CREATE TABLE newtable (a int)
GO
INSERT INTO newtable VALUES (10)
INSERT INTO newtable VALUES (20)
INSERT INTO newtable VALUES (30)
GO
DECLARE @counter int
SET @counter = 1
WHILE @counter < 1000 BEGIN
    UPDATE newtable SET a = a + 1
    SET @counter = @counter + 1
END

--step4:再次查看VLFs信息,VLFs的顺序是27,24,25,26.Status的顺序是2,0,0,2
DBCC LOGINFO

--step5:备份数据库
--定义备份设备
EXEC sp_addumpdevice 'disk', 'PubsBackup', 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\pubs.bak';
--step6:备份到逻辑设备
BACKUP DATABASE pubs TO PubsBackup;

--step7:再次查看VLFs信息,发现Status顺序变成了2,0,0,0.表示VLFs被截断.
DBCC LOGINFO
  

--step8:查看pubs数据库是不是出于自动截断模式,如果last_log_backup_lsn为null则表示自动截断模式 
--此时的last_log_backup_lsn=27000000015900042,这个LSN就是备份操作的first lsn,也就是日志不再自动搜索
SELECT last_log_backup_lsn
FROM master.sys.database_recovery_status
WHERE database_id = db_id('pubs')
GO

--step9:再次执行更新操作,发现日志文件增长,从原来的1024kb增长到1536kb
DECLARE @counter int
SET @counter = 1
WHILE @counter < 1000 BEGIN
    UPDATE newtable SET a = a + 1
    SET @counter = @counter + 1
END

--step10:查看VLFs,我们发现新增了两个VLFs.
DBCC LOGINFO

------------------------------------
--在2005中shrink日志文件的操作,在2008及以后版本不支持
DBCC SHRINKDATABASE (pubs)
BACKUP LOG pubs WITH TRUNCATE_ONLY--在2008以后不支持了
----------------------------------

--step11:2008以后shrink文件方法,切换恢复模式,然后执行收缩.最后切换回来.
USE pubs;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE pubs
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.成功将日志文件从1536kb缩小到1024kb,并且只有一个VLFs的Status=2
DBCC SHRINKFILE (2, 1);  -- here 2 is the file ID for trasaction log file,you can also mention the log file name (pubs_log)
GO
-- Reset the database recovery model.
ALTER DATABASE pubs
SET RECOVERY FULL;
GO
--------------------------------------------

在《inside sql server 2005: the storage engine》的“SIMPLE Recovery Model”章节提到一句话“In fact, as soon as you change you database to SIMPLE recovery model, the log will be truncated.”也就是一旦我们将数据库恢复模式修改成简单恢复模式，数据立刻截断日志。所以在我们上面的实验中，要收缩shrink日志，但是我们并没有进行截断truncate操作，其实截断操作已经隐含在切换数据库模式的那一步了。

还有在checkpoint的时候，数据库也会截断日志，截断的是MinLSN之前的VLFs。

 

作者：xwdreamer