也談如何縮小SQL SERVER記錄檔

 

1.

如何縮小MSSQL的記錄檔已經是一個經常性的問題了，不過這個問題在精華區已經有不少答案了，我這裡也不再贅述。

現在我們討論一下治本的問題，即如何使記錄檔不再增大？

先介紹一個簡單的方法。

就是把資料庫的故障還原模型設定為“簡單”(SQL2K)。這樣它就會在Checkpoint的時候截斷日誌。

具體操作方法是：

1、在Enterprise Manager中右鍵點資料庫，“屬性|選項|故障還原”，選擇“簡單”就可以了，如果是SQL7，在“屬性|選項”中有一個“trunc. log on chkpt. ”，選中就可以了。

2、如果不想用Enterprise Manager，在Query Analyser或者isql裡面執行

EXEC sp_dboption 'your_dbname', 'trunc. log on chkpt.', 'TRUE'

就可以了

但是，要注意的是，這樣做了之後，雖然日誌不會增大，但是也意味著你一旦出現誤操作，將不會有利用日誌恢複的機會。(如何利用日誌來恢複請參見精華區的FAQ)

所以，絕對不建議在生產資料庫上截斷日誌，除非你有充足的理由和足夠的把握，或者……

承擔責任的不是你。

既然這種方法不安全，下面我將介紹一種安全的方法。

大家都知道，SQL Server 在完成交易記錄備份時將自動截斷交易記錄中的不活動部分。這些不活動的部分包含已完成的事務，因此在恢複過程中不再使用。相反，交易記錄的活動部分包含仍在運行但尚未完成的事務。SQL Server 將重新使用交易記錄中這些截斷的非活動空間，而不是任由交易記錄繼續增大並佔用更多的空間。

所以，我們備份交易記錄就可以使記錄檔不再增大了。

但是呢，記錄檔一直放著也不是個辦法，刪除呢，又會失去恢複的可能性。

我們可以結合完全備份來做。做過完全備份之前的交易記錄就可以刪除了。

比如說，一個備份計劃，每天一次完全備份，保留7天內的，每15分鐘一次交易記錄備份，保留2天的。

用資料庫維護計劃嚮導可以很方便的建立備份計劃，不過一定要記得設定保留多久的備份哦，否則硬碟空間被備份給佔滿了就壞事了。

2.

前幾天也碰到記錄檔過大的問題，資料庫實際大小為600M, 記錄檔實際大小為33M, 但記錄檔佔用空間為2.8G!!!

試了多種方式，SHIRNK DATABASE， TRUNCATE LOG FILE, 都沒辦法將檔案縮小。無論如何，這應該算SQL SERVER的一個BUG吧。

後來找到下面的代碼，就可以將記錄檔縮小到自己想要的大小了。把代碼COPY到查詢分析器裡，，然後修改其中的3個參數(資料庫名，記錄檔名，和目標記錄檔的大小)，運行即可(我已經用過多次了)

-----

SET NOCOUNT ON

DECLARE @LogicalFileName sysname,

        @MaxMinutes INT,

        @NewSize INT

USE     Marias             -- 要操作的資料庫名

SELECT  @LogicalFileName = 'Marias_log',  -- 記錄檔名

@MaxMinutes = 10,               -- Limit on time allowed to wrap log.

        @NewSize = 100                  -- 你想設定的記錄檔的大小(M)

-- Setup / initialize

DECLARE @OriginalSize int

SELECT @OriginalSize = size

  FROM sysfiles

  WHERE name = @LogicalFileName

SELECT 'Original Size of ' + db_name() + ' LOG is ' +

        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +

        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'

  FROM sysfiles

  WHERE name = @LogicalFileName

CREATE TABLE DummyTrans

  (DummyColumn char (8000) not null)

DECLARE @Counter   INT,

        @StartTime DATETIME,

        @TruncLog  VARCHAR(255)

SELECT  @StartTime = GETDATE(),

        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'

DBCC SHRINKFILE (@LogicalFileName, @NewSize)

EXEC (@TruncLog)

-- Wrap the log if necessary.

WHILE     @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired

      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) 

      AND (@OriginalSize * 8 /1024) > @NewSize 

  BEGIN -- Outer loop.

    SELECT @Counter = 0

    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))

      BEGIN -- update

        INSERT DummyTrans VALUES ('Fill Log') 

        DELETE DummyTrans

        SELECT @Counter = @Counter + 1

      END  

    EXEC (@TruncLog) 

  END  

SELECT 'Final Size of ' + db_name() + ' LOG is ' +

        CONVERT(VARCHAR(30),size) + ' 8K pages or ' +

        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'

  FROM sysfiles

  WHERE name = @LogicalFileName

DROP TABLE DummyTrans

SET NOCOUNT OFF