今天檢視Job的History，發現Job 運作失敗，錯誤資訊是：“The transaction log for database 'xxxx' is full due to 'ACTIVE_TRANSACTION'.”
錯誤消息表明：資料庫的事務日志檔案空間耗盡，log 檔案不能再存儲新的transaction log。
SQL Server将事務日志檔案在邏輯上劃分為多個VLF（Virtual Log Files），将這些VLF組成一個的環形結構，以VLF為重用單元。如果一個VLF 中存在Active Transaction，那麼該VLF就不能被截斷和重用。如果事務日志檔案沒有可用的VLF，那麼SQL Server就不能處理新增的事務，并抛出事務日志檔案耗盡的錯誤消息。
那為什麼Active Transaction 會導緻事務日志檔案耗盡？
1，如果資料庫的事務日志檔案太大，将整個Disk Space耗盡，那麼就要考慮是什麼原因造成事務日志檔案大量增長，定期做事務日志備份能夠截斷事務日志檔案。
2，如果資料庫的事務日志檔案本身不是很大，可能的原因是SQL Server 無法為事務日志檔案配置設定Disk Space。
3，檢視資料庫中活動的事務，如果是由于一個事務運作時間太長，沒有關閉，導緻事務日志的VLF不能重用，那麼必須修改應用程式。
如果資料庫中某一個 Transaction 運作的時間太長，導緻其他transaction雖然被commint，但是其占用的VLF仍然被标記為Active，不能被truncate和reuse，當log檔案中沒有可用的VLF，而SQL Server又要處理新增的Transaction時，SQL Server就會報錯。
step1，檢視事務日志檔案的大小
檢視日志檔案的 size_gb 和 max_size_gb 字段，發現該事務日志檔案的大小沒有達到最大值，并且事務日志檔案占用的Disk Space并不是很大，我猜想，很可能是日志檔案所在的Disk Space 被使用殆盡，沒有剩餘的free space。
step2，檢視Disk的Free Space
查詢結果顯示，D盤空間僅僅剩下9MB，正是事務日志檔案所在的Disk。
step3，Disk Space 用盡，必須想辦法将大的資料檔案壓縮，或者将事務日志檔案截斷。
由于資料庫的恢複模式是simple，會自動截斷事務日志檔案，是以，最大的可能是disk space耗盡。
1，檢視資料庫空間的使用情況
unallocated space 空閑很大，必須壓縮資料庫，以釋放disk space
2，收縮（shrink）資料庫檔案
3，對資料庫中的 table 和 index 壓縮存儲
3.1， 檢視資料庫中，占用存儲空間非常大的table；
3.2， 檢視table及其Index是否被壓縮過
3.3，估計壓縮能夠節省的存儲空間
3.4， 對table及其index進行資料壓縮
對table 及其index 進行 rebuild，SQL Server将重新配置設定存儲空間，慎重：rebuild 反而會增加資料庫占用的存儲空間。在資料壓縮存儲之後，必須shrink 資料庫檔案，才能釋放資料庫所占用的存儲空間，增加Disk的Free Space。
4，增加事務日志檔案
SQL Server will return a log_reuse_wait_desc value of ACTIVE_ TRANSACTION if it runs out of virtual log files because of an open transaction. Open transactions prevent virtual log file reuse, because the information in the log records for that transaction might be required to execute a rollback operation.
To prevent this log reuse wait type, make sure you design you transactions to be as short lived as possible and never require end user interaction while a transaction is open.
To resolve this wait, you have to commit or rollback all transactions. The safest strategy is to just wait until the transactions finish themselves. Well-designed transactions are usually short lived, but there are many reasons that can turn a normal transaction into a log running one. If you cannot afford to wait for an extra-long running transaction to finish, you might have to kill its session. However, that will cause that transaction to be rolled back. Keep this in mind when designing your application and try to keep all transactions as short as possible.
One common design mistake that can lead to very long running transactions is to require user interaction while the transaction is open. If the person that started the transaction went to lunch while the system is waiting for a response, this transaction can turn into a very-long-running transaction. During this time other transactions, if they are not blocked by this one, will eventually fill up the log and cause the log file to grow.
本文版權歸作者和部落格園所有，歡迎轉載，但未經作者同意，必須保留此段聲明，且在文章頁面醒目位置顯示原文連接配接，否則保留追究法律責任的權利。
本文轉自悅光陰部落格園部落格，原文連結：http://www.cnblogs.com/ljhdo/p/5535750.html，如需轉載請自行聯系原作者