深入SQLServer日志收缩-阿里云开发者社区

问题背景

SQLServer的日志是DB级别的这点和MySQL不同，多个DB就需要维护多个日志文件并且每个DB的日志文件可以有多个，所以从空间资源的角度来说这给云上的用户带来一些困扰，毕竟在云上磁盘空间是要收费的，除了性能他们也非常关心这些日志何时截断重用、何时收缩

问题探讨

事务日志

在理解事务日志基本概念的基础上我们来做个有关收缩的测试：

    --1. 构建测试用的日志
    --    tpcc是我的测试DB，ITEM表是之前生成的一个测试表，这里不需要关心表内容
    USE [tpcc]
    GO
    DECLARE @VALUE INT
    SET @VALUE = 0
    WHILE @VALUE <= 100
    BEGIN
        SELECT * INTO ITEM_2 FROM ITEM
        DROP TABLE ITEM_2
           SET @VALUE = @VALUE + 1
    END
    GO    
    
    --2. 观察当前等待日志截断重用的原因和每个DB的日志使用量
    select log_reuse_wait_desc from sys.databases where name='tpcc'
    go
    dbcc sqlperf(logspace)
    go

    --3. 备份数据和日志
    BACKUP DATABASE TPCC TO DISK='D:\BACKUP\TPCC.BAK' WITH COMPRESSION,INIT
    BACKUP LOG TPCC TO DISK='D:\BACKUP\TPCC.TRN' WITH COMPRESSION,INIT
    
    --4. 再次观察当前等待日志截断重用的原因和每个DB的日志使用量
    select log_reuse_wait_desc from sys.databases where name='tpcc'
    go
    dbcc sqlperf(logspace)
    go

    --5. 收缩日志
    USE [tpcc]
    GO
    dbcc shrinkfile(log)

    --6. 查看收缩后的日志使用量        
    dbcc sqlperf(logspace)
    go

这一步也可以通过上一步收缩的结果计算出来（90112*8K = 704 MB）；

现在在理解基本概念的基础上产生了第一个疑问，为什么1G的日志使用了4%却只能收缩到704MB？

为了解答这个问题我们需要引入另一个概念 Virtual Log Files(VLF)，SQLServer为了方便日志管理，逻辑上将事务日志划分为多个虚拟日志文件，我们要讨论的收缩就是以虚拟日志文件为单位操作的。

具体可以参考日志的结构

我们再来重复一次刚才的测试，这次加入对VLFs的记录观察；

在刚才的1、3、5步骤之后记录如下SQL返回结果，帮助后续分析；

USE [tpcc]
GO    
dbcc loginfo

步骤1结束后

步骤3结束后

步骤5结束后

这里跟收缩日志最相关的是Stats字段，0表示inactive，2表示active，active状态的VLF通过checkpoint+日志备份可以转化为inactive，但有一个原则是header（可理解为最新的活动日志）部分只能后推或回绕（wraps around）不能move forward（跟offset相关）；

在开始备份前有11个活动VLF，checkpoint（数据备份的第一阶段）+ 备份日志后只有最新的VLF处于活动状态（日志截断并标记老的VLF为inactive-可重用），收缩后释放掉了未使用的VLF；

截止到这里截断和收缩实际已经达到了充分利用空间的目的，虽然直观看物理文件没有下降多少但新的日志已经开始回绕；但即便如此一些客户还在追求物理文件的进一步减少，实际想达到这个目的也很简单，依据之前讲的header部分已经回绕，再次备份收缩就可以了；

--再次备份
backup database tpcc to disk='d:\backup\tpcc.bak' with compression,init
backup log tpcc to disk='d:\backup\tpcc.trn' with compression,init

--再次收缩
USE [tpcc]
GO
dbcc shrinkfile(log)    

--观察VLF
USE [tpcc]
GO    
dbcc loginfo

为什么没有收缩最后一个inactive的VLF跟这个DB的日志初始化大小、shrinkfile的参数有关，简单说不回收最后一个VLF已经可以达到初始化的大小；

我们也可以通过VLF的总大小和物理文件对比做一个验证：

--通过FileSize计算要加8KB的页头                67043328+67043328+67043328+67043328+67043328+67043328+67043328+67559424+8192=536870912B=512MB

--通过最后一个偏移量算
469311488+67559424=536870912B=512MB

结论和建议

日志截断依赖于checkpoint和日志备份（FULL模式）
日志截断的含义是把VLF标记为可重用
日志收缩的多少需要看VLF的header和VLF的数量以及大小而不是通过sqlperf返回的结果判断（这也是很多DBA和用户误解的地方）
VLF的数量是事务日志创建时初始化好的一个初始值后续会随日志增长和增加，大小跟日志文件的大小、增长速度相关没有恒等的计算方法
在非云场景下（传统用户），不建议用户频繁做日志收缩去回收空间，因为这种收缩是有很大开销的，但肯定也不会一直增长，我们通过频繁的日志备份做截断、回绕日志文件以达到节省空间的目的；在云场景下（RDS），依然也不建议频繁收缩，但面对一些希望用其它资源换空间资源且业务场景允许的情况下，建议这部分用户使用OpenAPI定制化自己的备份策略