← 技術筆記

DBA筆記-日常小撇步-2.1-TransactionLog空間自動釋放

資料庫 · 2026/8/7

舊文搬家系列之五。

下面這個語法年代久遠,如果我沒記錯的話,應該是我自己編寫的。

而起因是我曾經待過一間硬體條件非常有限的銀行,整天都在爆空間,三天兩頭就一直被CALL,一直說硬碟爆了,我才被逼的開始尋求一些可以偷懶的小方法。

當時在系統環境不能異動,也不想買硬碟的情況下,就只能硬逼DB把殘餘空間吐出來。

但就如內文說的,這是一個方便的清理空間的語法,但不要把它當日常操作,除非你的公司逼你這樣做。


在這裡提供一個自動化的Transaction Log空間強制釋放語法。

並注意這裡所描述的做法並非標準處置手段,需視特定狀況來決定適不適用。

 

下方語法的運作邏輯如下:

1.檢查所有目前所有DB的Transaction Log使用空間,並特別紀錄使用空間已達1GB以上者。

2.使用DBCC Shrinkfile語法將這些Transaction Log檔案依比例縮小,但仍保留一個基本值並非全數歸零。

3.若上述步驟執行無效,則將DB的Recovery Mode修改為Simple後再執行一次相同語法。

4.若還是無效則代表目前Transaction Log正在使用狀態,結束這次執行流程。

--------------------

註1:本來最後還有一段DBCC Shrinkdatabase語法來對整個DB做空間釋放做為最後手段,但這個方式除對線上效能有明顯影響之外,最嚴重也可能中斷其它執行序的運作,所以這段目前是註解狀態,並且不建議啟用。

註2:Transaction Log的標準處置方式並非本文所描述的做法,正規方式應該是定期做log backup以釋放Transaction Log內部資料,並保留且循環使用該Transaction Log空間。

註3:本人之前所處的IT環境特別窮困而且緊迫,每台Server的硬碟空間都十分吃緊,同時IT的升級預算也十分有限。

也為了因應這種環境,DB備份方式也十分陽春,每日僅做一次DB full backup,而Transaction Log則全數捨棄以節省硬碟 空間。

在實務上,僅憑每日一次的DB full backup無法做到即時的資料災難備援,況且DB運作本來就需要一定量的Transaction空間做為資料操作用,逼迫DB吐出所有空間並非健康做法,而Transaction Log被強制縮小後也只是讓它重新成長一次,而這也會增加額外I/O時間,對效能只有負面影響。

所以再次強調本方案適用於特殊狀況,如果您所處的環境資源足夠的話,不建議使用本文方法。

----------------------

語法如下所示:

SELECT ROW_NUMBER() OVER (ORDER BY A.database_id) AS SN,

A.database_id,DB_NAME(A.database_id) AS DBNAME,B.recovery_model_desc,

FILE_ID,type_desc,A.name AS [FileName],size,size/128.0 AS HDSize

INTO #LOG_LIST

FROM sys.master_files A

JOIN  sys.databases B ON A.database_id=B.database_id

WHERE type_desc='LOG'

AND size/128.0 >=1000

 

DECLARE @RUN INT  SET @RUN = 1

DECLARE @END INT SELECT @END = MAX(SN) FROM #LOG_LIST

 

DECLARE @DB_Name VARCHAR(20)

DECLARE @DB_ID INT

DECLARE @FileID INT

DECLARE @BeforeSize DECIMAL(20,10)

DECLARE @TargetSize INT

DECLARE @NowSize DECIMAL(20,10)

DECLARE @RecoveryMode VARCHAR(10)

DECLARE @Shrink_Command VARCHAR(1000) SET @Shrink_Command = ''

 

 

WHILE @RUN<=@END

BEGIN

    --CHECK DB INFO

 SELECT @DB_Name =DBNAME,@DB_ID = database_id,@FileID = FILE_ID,

        @RecoveryMode = recovery_model_desc,@BeforeSize = HDSize

 FROM #LOG_LIST

 WHERE SN = @RUN

 

    --DECIDE TARGET SIZE

    IF @BeforeSize >=1000 SET @TargetSize = 500

    IF @BeforeSize >=3000 SET @TargetSize = 1000  

    IF @BeforeSize >=10000 SET @TargetSize = 5000

   

   

    --SHRINK  

    SET @Shrink_Command = 'USE ['+ @DB_Name +']  DBCC SHRINKFILE ('+CONVERT(VARCHAR(10),@FileID)+

                          ' ,'+CONVERT(VARCHAR(30),@TargetSize)+',TRUNCATEONLY)'

    PRINT @Shrink_Command

    EXEC (@Shrink_Command)

   

   

    --CHECK SIZE

 SELECT @NowSize = size/128.0

 FROM sys.master_files A

 WHERE type_desc='LOG'

 AND A.database_id  =@DB_ID

 AND FILE_ID = @FileID

 

    --SHRINK AGAIN FOR CHANGE RECOVERY MODE

    IF @NowSize =  @BeforeSize OR @NowSize > (@TargetSize+1000)

    BEGIN

       SET  @Shrink_Command =  'ALTER DATABASE ['+ @DB_Name +'] SET RECOVERY SIMPLE WITH NO_WAIT'

       PRINT @Shrink_Command

       EXEC (@Shrink_Command)     

 

       SET @Shrink_Command = 'USE ['+ @DB_Name +']  DBCC SHRINKFILE ('+CONVERT(VARCHAR(10),@FileID)+' ,'

                            +CONVERT(VARCHAR(30),@TargetSize)+',TRUNCATEONLY)'

       PRINT @Shrink_Command

       EXEC (@Shrink_Command)

 

      

       IF @RecoveryMode <> 'SIMPLE' 

       BEGIN

          SET  @Shrink_Command = 'ALTER DATABASE ['+@DB_Name+'] SET RECOVERY '+@RecoveryMode +' WITH NO_WAIT'

          PRINT @Shrink_Command

          EXEC (@Shrink_Command)

       END

    END

 

 

    --CHECK SIZE2

 SELECT @NowSize = size/128.0

 FROM sys.master_files A

 WHERE type_desc='LOG'

 AND A.database_id  =@DB_ID

 AND FILE_ID = @FileID

 

    --SHRINK AGAIN FOR DATABASE

    IF @NowSize =  @BeforeSize OR @NowSize > (@TargetSize+1000)

    BEGIN

        SET  @Shrink_Command = 'DBCC SHRINKDATABASE (['+  @DB_Name +'],1)'

        --PRINT @Shrink_Command

        --EXEC (@Shrink_Command)

    END

 

 SET @RUN = @RUN + 1

END

 

DROP TABLE #LOG_LIST

留言

還沒有留言,來當第一個吧。

寫留言