DBA筆記-日常小撇步-2.1-TransactionLog空間自動釋放
舊文搬家系列之五。
下面這個語法年代久遠,如果我沒記錯的話,應該是我自己編寫的。
而起因是我曾經待過一間硬體條件非常有限的銀行,整天都在爆空間,三天兩頭就一直被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
留言
還沒有留言,來當第一個吧。
寫留言