處理令人心煩的資料庫事務日志 (SQL Server Transaction Log Files)

經常, 我們會被過快增長的資料庫事務日志Transaction Log而困擾, 如果我們沒有正确及時的處理, 可能會造成資料庫交易無法進行, 伺服器磁盤空間占光等問題.

在SQL Server的使用過程中, 我經常幫助使用者和資料庫的維護人員處理日志Transaction Log太大後造成的系統癱瘓的問題. 其實這個問題很容易避免. 今天我給大家分享下, 是什麼造成了日志增長過大的問題. 和如何避免這種問題再次發生.

該文章的語句适用于SQL Server 2015 及其以後的版本

每一個資料庫至少有兩個檔案: 一個是資料檔案(Data file), 一個是事務日志檔案(Transaction Log file). 資料檔案存儲系統和使用者的資料, 而事務日志問題存儲所有的交易和由這些交易産生的資料庫的變更. 随着時間的推移, 越來越多的資料庫交易日志被保留下來.

如果你的資料的恢複模式 (Recovery Model) 設為Simple, 那麼當交易完成, 系統生成檢查點(Checkpoint) 後, 之前的事務日志會被标記為無效, 進而被資料庫自動截斷.

事務日志備份 Transaction Log Backups

如果你的資料庫恢複模式 (Recovery Model) 設為 Full 或 Bulk-Logged (通常資料庫的預設恢複模式時 Full), 是以你必須建立日志備 (Log Backup)來控制的事務日志 (注意, 日志備份之前一定要先做全備(Full Backup)).

你可以用一下語句檢視你資料庫的恢複模式

SELECT name, recovery_model_desc

FROM sys.databases

如果你沒有建立日志備份, 用完并失效的日志所占用的空間不會被重用, 那就意味着你的事務日志檔案随着交易的發生會越累越大. 你可以通過 database maintenance plan來建立一個定期的日志備份. 一旦有了日志的備份, 占用的日志空間就可以釋放出來被下一個交易利用, 這樣日志檔案就會在控制在一個一個合理的範圍.

如果事務日志備份出了問題, 也可能導緻事務日志過大, 你可以通過一下語句來檢視目前備份的情況.

SELECT name, log_reuse_wait_desc

長時間的活動交易 Long-Running Active Transactions

長時間的持續運作的交易也會阻止事務日志的釋放. 有些交易可能需要等待其他使用者輸入或者其他的資源, 導緻交易(Transaction)一直打開, 你可以通過一下語句檢視打開的交易.

DBCC OPENTRAN

傳回的結果有該交易的會話ID, SPID (Session_id), 你可以 通過一下語句檢視該會話執行的詳細資訊.

EXECUTE sp_who2 spid

如果你想知道更詳細的語句, 可以再通過DBCC INPUTBUFFER()來看到完整的執行語句.

DBCC INPUTBUFFER(spid)

日志複制 Transactional Replication

如果你開啟的日志複制功能, 基于日志複制的原理, 主Database事務日志在被成功複制到 distributor之前也是不能被截斷的. 如果 distributor 端有問題, 導緻

QQ賬号買賣

事務日志複制不成功.

你可以通過之前的 DBCC OPENTRAN 檢查是否有未完成的交易沒有複制成功, 或被打開很長時間.

資料庫鏡像 Database Mirroring

資料庫鏡像和日志複制有相似的問題, 如果事務日志不能送出的從資料庫 Mirror端, 主資料庫Principal的日志也是不是被釋放的. 如果那樣的話, 你可能需要斷掉Mirror, 截斷主資料的日志, 然後重做Mirror.

磁盤空間 Disk Space

如果你的伺服器磁盤空間不足, 導緻事務日志有問題, 但是截斷或記錄檔有需要額外的空間, 你可能最好的的辦法在磁盤空間足夠的驅動器上建立一個日志檔案, 然後進行後續的截斷操作.

ALTER DATABASE your_db_name Add LOG FILE

收縮日志檔案 Shrinking the File

一旦你定位了日志增長的問題, 并解決後, 該是收拾"爛攤子", 把日志重新收回到合理值.

你可以用SQL Server Management Studio 右鍵你的資料庫 --> Tasks --> Shrink --> Select Log Files. 或者你用下面的TSQL

SELECT name

FROM sys.database_files

WHERE type_desc = 'LOG'

找到Log File之後, 可以用一下DBCC指令收縮日志到制定的大小, 本例為1G

DBCC SHRINKFILE ('db_log_file_name', 1000) -- Target 1G

寫在最後

我列出來一些常見的問題導緻事務日志過大的問題, 和常用的解決辦法. 除了上述方法, 你最好對的關鍵資料庫的磁盤等關鍵名額加上門檻值報警, 這樣才有時間在事情來變得更糟之前解決這些問題.

希望以上能夠幫到你.

Make work and life simple.