無法收縮事務日誌

這裡有點讓人頭疼。

我有一個數據少於 1GB 的數據庫，但有一個 40GB 的日誌文件。事務日誌每天備份，這個數據庫上沒有很多活動；大約每週一次，它會記錄新的工資單資訊，然後將這些數據重複用於報告目的。數據庫設置為自動收縮。

執行sp_spaceused @updateusage = true產生以下資訊：

database_name   database_size   unallocated space
PayrollImports  39412.06 MB 105.00 MB

reserved    data    index_size  unused
321728 KB   278640 KB   42816 KB    272 KB

執行DBCC shrinkfile (N'PayrollImports_log', 1 , notruncate)產生以下結果：

DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
19  2   4991088 3456    4991088 3456

UsedPages… the和 the之間的差異EstimatedPages令人費解，但我繼續DBCC shrinkfile (N'PayrollImports_log', 1 , truncateonly)並得到：

DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
19  2   4991088 3456    4991088 3456

在這一點上什麼都沒有改變。日誌文件仍然是 40GB。所以我想，也許我有一些公開交易。執行dbcc opentran應驗證：

No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

廢話。好吧，也許我的索引是零散的。我將對它們進行碎片整理sp_msForEachTable 'DBCC indexdefrag([PayrollImports], ''?'')'並嘗試再次縮小：

DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
19  2   4991088 3456    4991088 3456

仍然沒有任何改變。好的，我用 重新索引怎麼樣sp_msForEachTable 'DBCC dbreindex([?])'？

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

…現在我們得到：

DbId    FileId  CurrentSize MinimumSize UsedPages   EstimatedPages
19  2   4991088 3456    4991088 3456

不用找了。好吧，怎麼樣sp_msForEachTable 'ALTER INDEX ALL ON [PayrollImports].[?] REBUILD WITH (FILLFACTOR = 10)'？

立即，這失敗了：

Cannot find the object "(One of my tables)" because it does not exist or you do not have permissions.

嗯？它在那裡，好的。我做了一個select top 10 * from (My table)，它空了。嗯，這根本不對。這是一個應該有超過 200 行的查找表。這可能是數據損壞問題嗎？我從我的開發環境中收集數據，重新插入。

但我沒有想法。我不能縮小這個東西。我還能嘗試什麼？為什麼我的 UsedPages 比我的 EstimatedPages 高得難以置信？這裡發生了什麼？

DBCC LOGINFO在數據庫的上下文中檢查您的輸出。這將向您顯示日誌文件中所有 VLF 的狀態。您的收縮操作不會將日誌文件減少到最後一個活動文件（狀態=2）之後。由於 VLF 是以順序、循環方式使用的，因此您需要執行事務日誌備份足夠多次，直到您的活動 VLF 位於文件中您要收縮到的物理點為止。

雖然您說數據庫不是很活躍，但每天一次的事務日誌備份似乎非常少見，並且根據針對該數據庫執行的事務類型，可能有助於該數據庫的持續增長。我建議至少每小時執行一次日誌備份。如果您不需要在數據庫中進行這種恢復並且只需要恢復到過去 24 小時內的某個時間，我建議您將數據庫轉換為SIMPLE模式並每天進行一次完整備份。

Mike Walsh在這裡提供了詳細的解釋。

您的重新索引對實際縮小或允許文件縮小幾乎沒有作用，實際上可能會阻礙它。這是因為重新索引會在您的日誌中創建額外的事務，這些事務將在您執行日誌備份之前一直處於活動狀態。

此外，一些最佳實踐建議：

• 關閉自動收縮。由於多種原因，這是一種非常糟糕的做法。通過啟用此“功能”，您可能會為自己製造更多問題。
• 永遠不要使用truncate_only. 這基本上使您的日誌無法用於恢復。此時您最好使用SIMPLE模式和完整備份。

引用自：https://dba.stackexchange.com/questions/49615