SQL SERVER ALWAYS ON 为什么日志无法dump

SQL SERVER 还有人用，对的，很多人都在用，尤其很多企业，非互联网的企业。那今天就说说 SQL SERVER ALWAYS ON 高可用集群中，为什么不切日志的问题。引起这篇文字的原因是有一个81G 都没有切除日志的 AWO集群。

虽然解决这个问题，可以三言两语就解决掉，但实际上不知道其中的原理，只会敲命令的模式，是不被推荐的。

SQL SERVER 和其他的数据库在日志方面不大一样，其中有一个概念叫 VLFS,每个物理事务日志文件在内部划分为许多虚拟日志文件(VLFs)。虚拟日志文件没有特定的大小，也不能指定物理日志文件中有多少个VLF,这些都是数据库引擎来操作的，但实际当中SQL SERVER 是建议你减少 VLF的数量，虽然你的LDF文件可能只有一个，但是LDF 里面的 VLF 的文件数量可能就与你的SQL SERVER  日志文件增长大小，与增长比率有关了。如果你想获取较少的VLF，那就别吝啬，经常看到有人为了减小日志，给红色圈的位置设置LDF的大小，呵呵，呵呵就是我对这样设置的表情，无知者无畏。

另外为什么增量要设置的比较大，原因就是这个VLF， 在申请扩大日志文件的时候，其实就是生成了一个VLF，如果设置的太小，例如有些人设置 1MB 的增量，想想如果有大量日志写入，对SQL SERVER 是一件多么奇怪的事情。

日志文件本身内部也是顺序型，当VLF 文件的开头被截断了，这就说明这块VLF 虚拟文件可以使用了，的事务日志开头的日志记录在日志结束时被截断，它就会回到开头，并覆盖之前的内容。  

那原理基本上明确了，首先第一点日志不能shrink 的就是在“小气鬼” 自作聪明的申请日志空间的“抠门”行为。如果我一个1000MB的日志文件里面都是1MB大小的VLF， 后面只要有一个VLF 文件日志不截断，你前边的日志都截断，他也无法释放磁盘空间给你的操作系统。

另外从另一个观点来看如果一个系统你只设置一个LDF文件，也是让你的系统日志空间不容易被收回的根源，原理就很简单了，自己想想就明白了。

所以建议是，1 SQL SERVER 日志文件，可以是多个，根据你的系统的繁忙程度和你对日志释放空间的“迫切心情”。

2 日志的增量设置，别太抠门

这样就能大概率的让单机上几十，上百G的磁盘空间有可能被释放回来，当然不释放也不用太担心，因为会继续循环使用。

当然如果想借用并行的概念到 SQL SERVER LDF 妄想通过多个文件，提高性能，那你就参见MYSQL 的BINLOG ，POSTGRESQL  WAL LOG，这方面的他们都是一样的，串行。

这时可能就有人问，到底为什么会有日志空间不足的情况，

1 未提交的事务

2 创建大表的索引

3 复制中没有复制过去的事务

4 长期运行的事务，也不给人家COMMIT 

5 特别大的事务，几百行，上千行，上万行的那种从 begin 到 commit 只有一个的奇葩。

下面是一个脚本，通过这个脚本，可以看到你当前的数据库可用的ldf的数据库空间是多少

DBCC SQLPERF(LOGSPACE) 

通过上面的命令可以看到总体的数据库日志占用的比率。

也可以通过下面的命令来查看 ldf 文件中的VLF 的情况

select * from sys.dm_db_log_info ( db_id('aap') )  

通过 vlf_active 和 vlf_status 两个字段可以清晰的看到 LDF 文件里面的那些VLF 是被激活的，那些是可以使用的。

通过上边的脚本我们就可以知道，在我们当前库里面的LDF 文件中，LDF 可以收缩的数量，并且能分析出在Active log  之前有多少日志是 FREE 有多少日志active log 在之后是 free的。

SELECT name AS 'Database Name', log_backup_time AS 'last log backup time' 

FROM sys.databases AS s

CROSS APPLY sys.dm_db_log_stats(s.database_id); 

上面也讲了，要切日志的话，有三点（单机）

1 数据库处于simple的模式

2 数据库做了FULL BACKUP

3 数据库在2的基础上做了 transaction log backup

回到题目到底有多少原因可以让日志无法进行transaction，

CHECKPOINT
LOG_BACKUP
ACTIVE_BACKUP_OR_RESTORE
ACTIVE_TRANSACTION
DATABASE_MIRRORING
REPLICATION
DATABASE_SNAPSHOT_CREATION
LOG_SCAN
AVAILABILITY_REPLICA
OLDEST_PAGE
XTP_CHECKPOINT
OTHER TRANSIEN

其实我们可以很简单将always on 中的数据库为什么不切断日志，在这个上面去寻找对应的问题点就可以了

以我现在所在的数据库AWO，log_truncation_holdup_reason的原因是log_backup, 那我们就去做log_transaction的backup 看看问题是否能解决

在我做完日志备份，并选择截断后。

再次去查看日志hold的原因，很清晰的显示nothing 也就是日志被截断了。

我们在对比这篇文字的上面的图可以看出在做了transaction backup 后，的确释放了5MB的空间。由于没有给AWO 主库做 TRANACTION BACKUP导致的日志不能被transaction log 不能被dump的问题解决了

是否还有其他的原因造成日志的空间不能被重复利用

那长时间运行的事务，例如一个存储过程写了上千行，运行一次就要几个小时的那种，很可能就会影响你的ACTIVE_TRANSACTION，如果发现系统经常显示ACTIVE_TRANSACTION，那就去和你的开发或者供应商来联系一下，是否存在这个问题。长事务无法完成，导致日志无法被截断冲利用，然后恶性循环。

除此以外，SQL SERVER AWO 备份如果想截断日志，则需要在主库上primary上操作，虽然可以在standby 从库上操作备份，FULL 或者 copy_ONLY的模式，但这样也是没有办法来将日志进行cut off dump的。

最后与AWO 有关日志无法dump 的原因还有就是数据的复制可能出现了问题AVAILABILITY_REPLICA，当显示log hold 是AVAILABILITY_REPLICA的情况下，如果所有的辅助副本都没有完成重做的日志记录处理，那么主副本上的日志备份不会截断日志。

这里还有一种情况就是 主机的配置高，多台副本中有配置低的机器，这样也会影响你的主库的日志dump所以当出现上面无法对主库日志dump的情况下Redo Byte Remaining是你的一个监控点。 所以这也是要求，AWO 的各个节点的配置要一致，从库所负担的，除了少了SELECT的操作，写的操作可以看做是主库的 DOUBLE。

其实还有一些问题没有说，但作为微信手机端查看的文字如果又臭又长，很难是有人看完的，所以今天就到此为止。

哦忘了，如果你在使用SQL SERVER 2016 , 2017 ,2019 会有一个error 9002的问题，导致日志无法回收，所以这也是数据库系统别求太新，当然补丁已经好了，可以去微软上下载并打上。