關於sql server 2012日誌變得超大的刪除解決辦法

標籤：sqlserver 刪日誌

首先說說,這個sql server機制其實挺嚴謹,他的原意是---如果你不做記錄備份,就不給你刪日誌,然後也有指令碼給你回收日誌空間,算是挺安全也方便實用了.

看起來是挺合理的,但是碰到缺心眼的開發或使用者,日誌一天天的脹大,而又忘記回收的話,那就悲催了,這個時候你就不可能備份了,因為硬碟空間不夠用啊,不能備份也就不能刪日誌,就成了個死迴圈了.

我這邊就遇到這種事,日誌被撐到170G了,硬碟總共才200G空間,怎麼搞好呢?

最後經過百度和google兩位幕後大師傅的指點,得到了下面的方法,僅供參考,因為也可能有些情況不太一致.

首先我們看看日誌當前的狀態,

DBCC LOGINFO(test9572)

可以看到status=0的日誌，代表已經備份到磁碟的記錄檔；而status=2的日誌還沒有備份。當收縮記錄檔時，收縮掉的空間其實就是 status=0的空間，如果日誌物理檔案無法減小，這裡一定能看到非常多status=2的記錄。

然後我們看看日誌截斷延遲原因,

SELECT [name] ,[database_id] ,[log_reuse_wait] ,[log_reuse_wait_desc] FROM [sys].[databases];

各種原因及解釋如下：

log_reuse_wait_desc 值

NOTHING    當前有一個或多個可重複使用的虛擬記錄檔。

CHECKPOINT    自上次日誌截斷之後，尚未出現檢查點，或者日誌頭部尚未跨一個虛擬記錄檔移動（所有復原模式）。

這是日誌截斷延遲的常見原因。

LOG_BACKUP    需要記錄備份，以將日誌的頭部前移（僅適用於完整復原模式或大量記錄復原模式）。

注意：記錄備份不會妨礙截斷。

完成記錄備份後，日誌的頭部將前移，一些日誌空間可能變為可重複使用。

ACTIVE_BACKUP_OR_RESTORE    資料備份或還原進行中（所有復原模式）。資料備份與活動事務的運行方式相同。資料備份在運行時，將阻止截斷。

ACTIVE_TRANSACTION    事務處於活動狀態（所有復原模式）。一個長時間啟動並執行事務可能存在於記錄備份的開頭。在這種情況下，可能需要進行另一個記錄備份才能釋放空間。

看完了狀態,我的問題也是這個LOG_BACKUP,日誌沒備份導致,也就是開頭說的死迴圈,因為硬碟空間不夠用啊,不能備份也就不能刪日誌,就成了個死迴圈了

總有解決辦法,辦法的原理是在簡單模式下進行，等清除動作完畢再調回到完全模式,下面來看:

首先,我們要確認日誌的檔案名稱,因為硬碟上的檔案名稱不一定是資料字典裡面的檔案名稱,所以要確認下

USE test9572GOSELECT file_id,name FROM sys.database_files;GO

然後就可以準備刪了:

USE [test9572]GOALTER DATABASE test9572 SET RECOVERY SIMPLE WITH NO_WAITGO--簡單模式ALTER DATABASE test9572 SET RECOVERY SIMPLE GOUSE test9572GODBCC SHRINKFILE (N‘test9572_log‘ , 11, TRUNCATEONLY) GOUSE [test9572]GOALTER DATABASE test9572 SET RECOVERY FULL WITH NO_WAITGO--還原為完全模式ALTER DATABASE test9572 SET RECOVERY FULL GO

刪完可以看看硬碟空間,一切都變好了,

關於sql server 2012日誌變得超大的刪除解決辦法