SQL SERVER ALWAYS ON 為什么日志無法dump

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的問題，導致日志無法回收，所以這也是數據庫系統別求太新，當然補丁已經好了，可以去微軟上下載并打上。

看完上述內容，你們掌握SQL SERVER  ALWAYS ON 為什么日志無法dump的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！