SQL2008R2 收縮日志檔案

---先備份資料庫(含日志檔案）

use myhis

go

backup database myhis to disk='d:\myhis_rzbak'

go

---設為簡單恢複模式

use [master]

go

alter database myhis set recovery simple with no_wait

go

alter database myhis set recovery simple

go

---收縮資料庫日志檔案為8M

use myhis

go

dbcc shrinkfile(myhis_log,8)

go

---重新設為完整恢複模式

use master

go

alter database myhis set recovery full with no_wait

go

alter database myhis set recovery full

go

SQL Server 2008 事務日志實體檔案尺寸無法減小的解決辦法（含日志收縮(shrink)技巧）

作者：宋林

發現有的資料庫日志檔案太大，無論如何收縮執行幾次SQL語句都不行。事務日志達30+G，而且使用正常的截斷、收縮方法均無法減小日志實體檔案的尺寸，經過一番尋找，終于找到了解決方法。

檢視日志資訊 

在查詢分析器中執行如下代碼來檢視日志資訊：

 DBCC LOGINFO('資料庫名稱') 

 我們看到status=0的日志，代表已經備份到磁盤的日志檔案；而status=2的日志還沒有備份。當我們收縮日志檔案時，收縮掉的空間其實就是status=0的空間，如果日志實體檔案無法減小，這裡一定能看到非常多status=2的記錄。接下來分析為什麼會有這麼多status=2的記錄

檢視日志截斷延遲原因

活躍(active)的日志無法通過收縮來截斷，有各種原因會使日志截斷延遲，具體表現就是事務日志的實體檔案無法通過截斷、收縮來減小，通過下面的代碼可以看到執行個體上每個資料庫的日志截斷延遲原因：

1 USE [master]

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	事務處于活動狀态（所有恢複模式）。 一個長時間運作的事務可能存在于日志備份的開頭。在這種情況下，可能需要進行另一個日志備份才能釋放空間。有關詳細資訊，請參閱本主題後面的“長時間運作的活動事務”部分。 事務被延遲（僅适用于 SQL Server 2005 Enterprise Edition 及更高版本）。“延遲的事務”是有效的活動事務，因為某些資源不可用，其復原受阻。有關導緻事務延遲的原因以及如何使它們擺脫延遲狀态的資訊，請參閱延遲的事務。
DATABASE_MIRRORING	資料庫鏡像暫停，或者在高性能模式下，鏡像資料庫明顯滞後于主體資料庫（僅限于完整恢複模式）。 有關詳細資訊，請參閱本主題後面的“資料庫鏡像與事務日志”部分。
REPLICATION	在事務複制過程中，與釋出相關的事務仍未傳遞到分發資料庫（僅限于完整恢複模式）。 有關詳細資訊，請參閱本主題後面的“事務複制與事務日志”部分。
DATABASE_SNAPSHOT_CREATION	正在建立資料庫快照（所有恢複模式）。 這是日志截斷延遲的常見原因，通常也是主要原因。
LOG_SCAN	正在進行日志掃描（所有恢複模式）。 這是日志截斷延遲的常見原因，通常也是主要原因。

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

• LOG_BACKUP

  備份日志後再執行收縮即可backup log [database] with nolog

• REPLICATION

  這是我遇到的情況，但我根本沒有啟用過REPLICATION，據查，這好像是SQLSERVER2008的一個BUG，解決方法是給标有“REPLICATION”的資料庫任意一個表建立資料庫事務複制（TRANSACTION REPLICATION），然後再删除，執行資料庫與日志備份後，就可以收縮了。

·         收縮日志小技巧

• 一般收縮日志的代碼中都要求指定日志的檔案名稱，下面的代碼則可以自動擷取日志檔案名稱：

• 1 USE [資料庫名稱]

  2 DECLARE@LogFileLogicalName sysname

  3 SELECT@LogFileLogicalName=Name FROM sys.database_files WHERE Type=1

  4 PRINT@LogFileLogicalName

  5 DBCC SHRINKFILE (@LogFileLogicalName, 1);

然後再收縮資料庫

DBCC SHRINKDATABASE(庫名)