Automatic Truncation of Virtual Log Files(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
--------------------------------------------