關于sql server日志變得超大的刪除解決辦法

    首先說說,這個sql server的日志,就是*log.ldf結尾的日志文件,他的日志體系結構其實挺嚴謹,原意是---如果你不做日志備份,就不給你刪日志,然后也有腳本給你回收日志空間,算是挺安全也方便實用了.

    看起來是挺合理的,但是碰到缺心眼的開發(fā)或使用者,日志一天天的脹大,而又忘記回收的話,那就悲催了,這個時候你就不可能備份了,然后就清空不了log.ldf日志,因為硬盤空間不夠用啊,不能備份也就不能刪日志,就成了個死循環(huán)了.

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

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

我們平常用的方法是直接收縮:

–SQL   Server收縮方法

最直接就是在sql server控制界面操作:

右鍵數(shù)據(jù)庫→任務→收縮數(shù)據(jù)庫→確定；

如果還是不理想,又或者試試這樣:

1、右鍵數(shù)據(jù)庫→屬性→選項→故障還原模型→設為簡單→確定；
2、右鍵數(shù)據(jù)庫→任務→收縮數(shù)據(jù)庫→確定；
3、右鍵數(shù)據(jù)庫→屬性→選項→故障還原模型→設為大容量日志記錄→確定。

再不行,就要用下面的方法了:

首先我們看看日志當前的狀態(tài),

DBCC LOGINFO(test9572)

可以看到status=0的日志，代表已經(jīng)備份到磁盤的日志文件；而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    自上次日志截斷之后，尚未出現(xiàn)檢查點，或者日志頭部尚未跨一個虛擬日志文件移動（所有恢復模式）。

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

LOG_BACKUP    需要日志備份，以將日志的頭部前移（僅適用于完整恢復模式或大容量日志恢復模式）。

注意：日志備份不會妨礙截斷。

完成日志備份后，日志的頭部將前移，一些日志空間可能變?yōu)榭芍貜褪褂谩?/p>

ACTIVE_BACKUP_OR_RESTORE    數(shù)據(jù)備份或還原正在進行（所有恢復模式）。數(shù)據(jù)備份與活動事務的運行方式相同。數(shù)據(jù)備份在運行時，將阻止截斷。

ACTIVE_TRANSACTION    事務處于活動狀態(tài)（所有恢復模式）。一個長時間運行的事務可能存在于日志備份的開頭。在這種情況下，可能需要進行另一個日志備份才能釋放空間。

看完了狀態(tài),我的問題也是這個LOG_BACKUP,日志沒備份導致,也就是開頭說的死循環(huán),因為硬盤空間不夠用啊,不能備份也就不能刪日志,就成了個死循環(huán)了

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

首先,我們要確認日志的文件名,因為硬盤上的文件名不一定是數(shù)據(jù)字典里面的文件名,所以要確認下

USE test9572
GO
SELECT file_id,name FROM sys.database_files;
GO

然后就可以準備刪了:

USE [test9572]
GO
ALTER DATABASE test9572 SET RECOVERY SIMPLE WITH NO_WAIT
GO
--簡單模式
ALTER DATABASE test9572 SET RECOVERY SIMPLE 
GO
USE test9572
GO
DBCC SHRINKFILE (N'test9572_log' , 11, TRUNCATEONLY) 
GO
USE [test9572]
GO
ALTER DATABASE test9572 SET RECOVERY FULL WITH NO_WAIT
GO
--還原為完全模式
ALTER DATABASE test9572 SET RECOVERY FULL 
GO

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