[SQL][procedure] 壓縮交易記錄檔(Transaction Log File)懶人包
※ 2014-06-10 更新到 v 1.1 版:
將 USE DB 放在與SHRINKFILE同一批次內,可以統一建立此預存程序在 master 資料庫使用。
有的時候突然發現網頁或是 AP 資料寫不進資料庫,細查之下才發現 Log 太久沒清,導致 LDF 檔肥到吃滿所有硬碟空間,當務之急就是要趕快清 Log,多台伺服器和資料庫,要分版本,有些資料庫交易紀錄檔的邏輯名稱還不給你按照規矩來命名,清個 Log 要查東查西很麻煩?
試試看這包壓縮交易記錄檔(Transaction Log File)懶人包吧,他有一些特點或許是你要的:
1. 自動判別 MSSQL 版本下壓縮語法
(判別 MSSQL 版本需要引用之前寫的 dbo.fn_getSQLServerVersion() 函數)
2. 自動判別資料庫交易紀錄檔的邏輯名稱
3. 可指定資料庫做壓縮
語法如下:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Henry
-- Author URL: http://www.dotblogs.com.tw/henryli/
-- Script Version: v 1.1
-- MSSQL Version: 2000+
-- Create date: 2014-06-05
-- Update date: 2014-06-10
-- Description: 清交易記錄檔 Log 懶人包
-- Require: dbo.fn_getSQLServerVersion() --取得SQL Server 版本
-- =============================================
IF OBJECT_ID('dbo.usp_shrinkLog') IS NOT NULL
DROP PROCEDURE dbo.usp_shrinkLog;
GO
CREATE PROCEDURE dbo.usp_shrinkLog(@databaseName NVARCHAR(128) = NULL)
AS
SET NOCOUNT ON;
DECLARE @version VARCHAR(10);
DECLARE @logName VARCHAR(128);
SET @version = dbo.fn_getSQLServerVersion(); --取得SQL Server 版本
IF @databaseName IS NULL
SET @databaseName = DB_NAME();
IF @version IN ('2000', '2005')
BEGIN
--設定記錄檔邏輯名稱
SET @logName = (SELECT name FROM master.dbo.sysaltfiles WHERE dbid = DB_ID(@databaseName) and fileid = 2);
EXEC('
USE [' + @databaseName + ']
DBCC SHRINKFILE(' + @logName + ', 1)
BACKUP LOG [' + @databaseName + '] WITH TRUNCATE_ONLY
DBCC SHRINKFILE(' + @logName + ', 1)'
);
END
ELSE
BEGIN
--設定記錄檔邏輯名稱
SET @logName = (SELECT name FROM sys.master_files WHERE database_id = DB_ID(@databaseName) and type = 1);
EXEC('
USE [' + @databaseName + ']
ALTER DATABASE [' + @databaseName + '] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(' + @logName + ', 1)
ALTER DATABASE [' + @databaseName + '] SET RECOVERY FULL WITH NO_WAIT
');
END
GO
使用範例 (兩種擇一即可):
USE DBName
GO
EXEC dbo.usp_shrinkLog;
--壓縮指定資料庫(DBName)
USE master;
GO
EXEC dbo.usp_shrinkLog DBName;
參考資料:
本著作係採用創用 CC 姓名標示-相同方式分享 4.0 國際 授權條款授權,文章歡迎轉載,請註明出處,謝謝~~~