SQL Server 2008 事務(wù)日志物理文件尺寸無法減小的解決辦法（含日志收縮(shr...

命運之輪 2011-01-29

展開全文

前言

SQLServer2008中如何收縮日志文件，在邀月工作室的日志已經(jīng)詳細描述（點擊查看），這里不再贅述。本文的重點是與大家分享日志截斷、收縮后，物理文件的尺寸依然沒有減小到期望尺寸的問題。

解決方案

我的一個數(shù)據(jù)庫，數(shù)據(jù)文件10+G ，事務(wù)日志達20+G，而且使用常規(guī)的截斷、收縮方法均無法減小日志物理文件的尺寸，經(jīng)過一番尋找，終于找到了解決方法。

查看日志信息

在查詢分析器中執(zhí)行如下代碼來查看日志信息：

1 DBCC LOGINFO('數(shù)據(jù)庫名稱')

我們看到status=0的日志，代表已經(jīng)備份到磁盤的日志文件；而status=2的日志還沒有備份。當(dāng)我們收縮日志文件時，收縮掉的空間其實就是status=0的空間，如果日志物理文件無法減小，這里一定能看到非常多status=2的記錄。接下來分析為什么會有這么多status=2的記錄

查看日志截斷延遲原因

活躍(active)的日志無法通過收縮來截斷，有各種原因會使日志截斷延遲，具體表現(xiàn)就是事務(wù)日志的物理文件無法通過截斷、收縮來減小，通過下面的代碼可以看到實例上每個數(shù)據(jù)庫的日志截斷延遲原因：

1 USE [master]
2 SELECT [name] ,[database_id] ,[log_reuse_wait] ,[log_reuse_wait_desc] FROM [sys].[databases]

各種原因及解釋如下：

log_reuse_wait_desc 值	說明
NOTHING	當(dāng)前有一個或多個可重復(fù)使用的虛擬日志文件。
CHECKPOINT	自上次日志截斷之后，尚未出現(xiàn)檢查點，或者日志頭部尚未跨一個虛擬日志文件移動（所有恢復(fù)模式）。這是日志截斷延遲的常見原因。有關(guān)詳細信息，請參閱檢查點和日志的活動部分。
LOG_BACKUP	需要日志備份，以將日志的頭部前移（僅適用于完整恢復(fù)模式或大容量日志恢復(fù)模式）。注意：日志備份不會妨礙截斷。完成日志備份后，日志的頭部將前移，一些日志空間可能變?yōu)榭芍貜?fù)使用。
ACTIVE_BACKUP_OR_RESTORE	數(shù)據(jù)備份或還原正在進行（所有恢復(fù)模式）。數(shù)據(jù)備份與活動事務(wù)的運行方式相同。數(shù)據(jù)備份在運行時，將阻止截斷。有關(guān)詳細信息，請參閱本主題后面的“數(shù)據(jù)備份操作與還原操作”部分。
ACTIVE_TRANSACTION	事務(wù)處于活動狀態(tài)（所有恢復(fù)模式）。一個長時間運行的事務(wù)可能存在于日志備份的開頭。在這種情況下，可能需要進行另一個日志備份才能釋放空間。有關(guān)詳細信息，請參閱本主題后面的“長時間運行的活動事務(wù)”部分。事務(wù)被延遲（僅適用于 SQL Server 2005 Enterprise Edition 及更高版本）。“延遲的事務(wù) ”是有效的活動事務(wù)，因為某些資源不可用，其回滾受阻。有關(guān)導(dǎo)致事務(wù)延遲的原因以及如何使它們擺脫延遲狀態(tài)的信息，請參閱延遲的事務(wù)。
DATABASE_MIRRORING	數(shù)據(jù)庫鏡像暫停，或者在高性能模式下，鏡像數(shù)據(jù)庫明顯滯后于主體數(shù)據(jù)庫（僅限于完整恢復(fù)模式）。有關(guān)詳細信息，請參閱本主題后面的“數(shù)據(jù)庫鏡像與事務(wù)日志”部分。
REPLICATION	在事務(wù)復(fù)制過程中，與發(fā)布相關(guān)的事務(wù)仍未傳遞到分發(fā)數(shù)據(jù)庫（僅限于完整恢復(fù)模式）。有關(guān)詳細信息，請參閱本主題后面的“事務(wù)復(fù)制與事務(wù)日志”部分。
DATABASE_SNAPSHOT_CREATION	正在創(chuàng)建數(shù)據(jù)庫快照（所有恢復(fù)模式）。這是日志截斷延遲的常見原因，通常也是主要原因。
LOG_SCAN	正在進行日志掃描（所有恢復(fù)模式）。這是日志截斷延遲的常見原因，通常也是主要原因。

針對延遲日志截斷原因的部分解決方案

LOG_BACKUP
備份日志后再執(zhí)行收縮即可
REPLICATION
這是我遇到的情況，但我根本沒有啟用過REPLICATION，據(jù)查，這好像是SQLSERVER2008的一個BUG，解決方法是給標(biāo)有“REPLICATION”的數(shù)據(jù)庫任意一個表創(chuàng)建數(shù)據(jù)庫事務(wù)復(fù)制（TRANSACTION REPLICATION），然后再刪除，執(zhí)行數(shù)據(jù)庫與日志備份后，就可以收縮了。

小技巧

一般收縮日志的代碼中都要求指定日志的文件名稱，下面的代碼則可以自動獲取日志文件名稱：

1 USE [數(shù)據(jù)庫名稱]
2 DECLARE @LogFileLogicalName sysname
3 SELECT @LogFileLogicalName=Name FROM sys.database_files WHERE Type=1
4 PRINT @LogFileLogicalName
5 DBCC SHRINKFILE (@LogFileLogicalName, 1);