摘要:SQL Server 2000資料庫的日誌檔過大，如何將其縮小？
SQL Server 2000資料庫的日誌檔過大，如何將其縮小？
發表人:taitoulong | 發表時間: 2006年三月23日, 22:29
SQL Server 2000資料庫的事務日誌檔過大，如何將其縮小？
問題：SQL Server 2000資料庫的事務日誌檔過大，如何將其縮小？
解答：在SQL Server中，所有對資料庫執行的更新操作都會記錄在資料庫的事務日誌檔中，除非將資料庫設為可自動收縮的或手動的對資料庫進行了收縮，否則事務日誌檔將一直增長，直到達到事先設定的日誌檔增長上限或用盡所有可用的磁碟空間。
如果當前的資料庫檔或日誌檔過大，可以使用以下兩個命令對其進行收縮：
DBCC SHRINKDATABASE：收縮指定資料庫的所有資料和日誌檔的大小
DBCC SHRINKFILE：收縮資料庫的某個指定資料或日誌檔的大小
這兩個命令可以釋放資料庫中的空閒空間，並將資料庫或指定的資料庫檔收縮到指定的大小，但收縮後的資料檔案或日誌檔的大小不會小於檔中現存的有效資料所佔空間的大小。
關於這兩個命令的具體使用方法，可以參考SQL Server 2000聯機叢書中的相應主題。另外，也可在SQL Server企業管理器中執行資料庫收縮，同樣是調用的以上兩個命令，效果類似。
在使用以上命令收縮日誌檔的時候需要注意，已寫入資料庫但未被截斷的事務日誌記錄是不會被收縮的，因為雖然這部分日誌記錄的資訊已經寫入資料庫檔，但在使用事務日誌備份進行資料庫還原的時候，還將用到其中的資訊。
對於使用簡單恢復模型的資料庫，事務日誌會在每次處理檢查點（CheckPoint）時自動被截斷。對於使用完全恢復模型或大容量日誌記錄恢復模型的資料庫，事務日誌只有在執行日誌備份（BACKUP LOG）時才會被截斷，這時事務日誌中記錄的資訊被寫入事務日誌備份檔案，而它們所佔用的這部分空間被標記為可用（即被截斷）。
截斷事務日誌並不會使日誌檔變小，但可以將其中的部分空間釋放供以後寫入新的日誌記錄使用。若要減少日誌檔的物理大小，則要使用上面提到的DBCC SHRINKDATABASE和DBCC SHRINKFILE命令。
在執行BACKUP LOG語句的時候，還可以使用WITH NO_LOG（或WITH TRUNCATE_ONLY，含義相同）參數，這時並不真正備份事務日誌，而只是截斷事務日誌中的非活動部分（這和普通的BACKUP LOG語句作用相同）。這適合於剩餘磁碟空間不夠進行事務日誌備份或不打算保留事務日誌中的非活動部分用於資料庫恢復的情況。
為避免事務日誌檔增長過快以致用盡所有磁碟空間的現象發生，一種辦法是將資料庫設為使用簡單恢復模型，這樣可以使SQL Server週期性的自動截斷事務日誌的非活動部分，並回收其佔用的空間供以後寫入事務日誌記錄使用。但這將使資料庫無法利用事務日誌備份還原到即時點，降低了資料庫的可靠性，因此一般不應用於生產型資料庫。
對於生產型資料庫，推薦的做法是使用完全恢復模型，並定期進行資料庫的完全備份和事務日誌備份。例如每週執行一次完全備份，每天執行一次事務日誌備份，這可以通過SQL Server企業管理器中的資料庫維護計畫嚮導很方便的實現（一般可以設為在每天夜裡業務不繁忙的某個時刻自動執行備份）。
通過定期執行資料庫的事務日誌備份，可以避免日誌檔的迅速增大，而使其保持一個比較穩定的大小。
雖然資料庫備份檔案也會佔用很多磁碟空間，但隨時可以將這些檔移到其他磁片上或在不需要它們的時候將其刪除，而且可以在出現故障或誤操作的時候方便的進行資料庫的還原。
由於資料檔案的大小是隨資料庫中資料量的增長而增長的，資料庫中已刪除的資料所佔的空間可以供新插入的資料使用；而在定期執行了事務日誌的備份後，我們可以將日誌檔的大小控制在一個比較合理的範圍。因此，一般不需要對資料庫進行收縮，也不推薦將資料庫設為自動收縮模式。建議僅在以下情況下執行資料庫的收縮：
1、磁碟空間不足
2、資料檔案很大，但其中只包含較少量的資料（可能是以前有大量資料，但後來刪除了很多），並且預期今後資料庫中的資料量也不會很大。
3、由於長期未進行事務日誌備份，導致事務日誌檔過大。
減小事務日誌檔大小的另一種方法是：首先在該資料庫中執行CHECKPOINT命令，然後將該資料庫分離（Detach），再將與其對應的資料庫日誌檔（.ldf檔）改名或刪除或移動到其他目錄下，然後執行sp_attach_single_file_db存儲過程或在企業管理器中重新將其附加（Attach）。由於找不到原來的日誌檔，SQL Server將自動為該資料庫建立一個大小只有504K的日誌檔。但這種方法必須暫時將資料庫脫機，因此一般不適宜在生產環境中使用。
如果當前資料庫的事務日誌檔過大，必須對其進行收縮的話，建議參照以下步驟：
1、建議首先備份資料庫（但不是必需的）：
BACKUP DATABASE database_name TO backup_device
2、備份事務日誌：
BACKUP LOG database_name TO backup_device如果不需要當前事務日誌中的記錄進行資料庫還原或沒有足夠的空間進行事務日誌備份的的話，也可僅執行以下命令截斷事務日誌：
BACKUP LOG database_name WITH NO_LOG
3、收縮日誌檔：
DBCC SHRINKFILE (log_file_name)其中log_file_name是事務日誌檔的邏輯名稱，可以在企業管理器中數據庫屬性的「事務日誌」頁中看到（如Northwind資料庫的默認事務日誌檔邏輯名稱為Northwind_log）。
4、如果日誌檔仍然較大的話，可以嘗試重複執行一次BACKUP LOG WITH NO_LOG和DBCC SHRINKFILE命令。
5、如果這時仍沒有明顯的效果，請執行DBCC OPENTRAN (database_name)檢查當前資料庫中是否存在長時間未提交的活動事務。有必要的話，可以斷開這些連接並重新嘗試截斷事務日誌和收縮日誌檔。
6、日誌檔收縮完成後，建議立即執行一次資料庫的完全備份並根據實際需要制定適當的資料庫備份計畫。
請大家永躍參與Facebook MSBI 粉思團:http://www.facebook.com/#!/group.php?gid=303757165010