MSSQL收縮事務日志&日志檔案過大無法收縮

原文: MSSQL收縮事務日志&日志檔案過大無法收縮 一.MS SQL SERVER 2005

--1.清空日志  

     exec('DUMP TRANSACTION 資料庫名 WITH NO_LOG')

--2.截斷事務日志：

     exec('BACKUP LOG 資料庫名 WITH NO_LOG')

--3.收縮資料庫檔案(如果不壓縮,資料庫的檔案不會減小

     exec('DBCC SHRINKDATABASE(資料庫名) ')

--4.設定自動收縮

     exec('EXEC sp_dboption 資料庫名,autoshrink,TRUE') 

       

二.MS SQL SERVER 2008 &2008r2 &2012 &2016

--在SQL2008中清除日志就必須在簡單模式下進行，等清除動作完畢再調回到完全模式。
USE [master]
GO
ALTER DATABASE 資料庫名 SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE 資料庫名 SET RECOVERY SIMPLE   --簡單模式
GO
USE 資料庫名 
GO
--crm50sp1_log 為資料庫日志檔案邏輯名
DBCC SHRINKFILE (N'crm50sp1_log' , 11, TRUNCATEONLY)
GO
USE [master]
GO
ALTER DATABASE 資料庫名 SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE 資料庫名 SET RECOVERY FULL  --還原為完全模式
GO      

三. 日志檔案異常大無法收縮的問題

     如果日志檔案異常大，且收縮的時候無法收縮，這個時候，需要檢視是否有未送出或者復原的事務

     執行DBCC OPENTRAN 指令，檢視是否有很早運作的事務（消息中顯示的有事務開始時間），顯示出來的都表示該事務沒有送出或復原是以一直導緻MinLSN無法前滾。

如果有這種的話，可以兩種方法，一通過資訊顯示的程序号，殺掉該程序。kill 程序号;(當然重新開機SQL服務也行，如果允許停機操作的話)；

    如果沒有長時間未關閉的事務， 簡單模式： 先備份資料庫，然後執行BACKUP LOG 資料庫名稱 WITH NO_LOG 完整模式： 如果沒有進行過完整備份，先進行一次完整備份，然後再備份日志檔案，最後執行 DBCC SHRINKFILE (N'日志檔案邏輯名稱' , 0, TRUNCATEONLY)隻收縮日志檔案

    日志檔案邏輯名稱通過以下語句可以得到 USE erp資料庫 go SELECT [name] FROM sys.database_files WHERE type_desc='LOG'