[SQL SERVER][Maintain]管理交易記錄檔(1)

2012-10-19

12744
0
SQL_SERVER


交易記錄檔暴增問題是我常常看到的問題，


大多數情況交易記錄檔大小往往都是資料庫大小數十倍以上，


如果DBA放任不管理交易記錄檔的話，


過大的交易記錄檔會影響DB Mirroring、Log Shipping、CDC、Replication、備份效能...等，


嚴重將導致資料庫成為唯讀狀態(硬碟空間被吃爆，SQL Server拋出9002錯誤)，


所以管理交易記錄檔可說是相當重要。


論壇網友詢問在做Tables大量寫入,更新,計算,刪除時希望log不會變很大?


因為我大多數的case都不允許我更改資料庫復原模式，


所以我這裡簡單測試在完整復原模式下的方法。


建立交易記錄檔初始大小=60MB 成長量=10MB，RecoveryMode=Full

建立來源資料表(新增40萬筆後交易記錄檔使用量)


SELECT instance_name AS DatabaseName, 
       [Data File(s) Size (KB)], 
       [LOG File(s) Size (KB)], 
       [Log File(s) Used Size (KB)], 
       [Percent Log Used] 
FROM 
( 
   SELECT * 
   FROM sys.dm_os_performance_counters 
   WHERE counter_name IN 
   ( 
       'Data File(s) Size (KB)', 
       'Log File(s) Size (KB)', 
       'Log File(s) Used Size (KB)', 
       'Percent Log Used' 
   ) 
     AND instance_name != '_Total' 
) AS Src 
PIVOT 
( 
   MAX(cntr_value) 
   FOR counter_name IN 
   ( 
       [Data File(s) Size (KB)], 
       [LOG File(s) Size (KB)], 
       [Log File(s) Used Size (KB)], 
       [Percent Log Used] 
   ) 
) AS pvt 
WHERE instance_name='demo'

建立來源資料表並匯入資料，交易記錄檔使用16999(KB)27%。

接下來模擬網友資料操作過程

Create


CREATE TABLE [dbo].[MyLOG1](
    [LogId] [int] identity(1,1) NOT NULL,
    agentname varchar(30) not null,
    [LogDate] [datetime] NOT NULL,    
    constraint PK_MyLOG1 primary key(LogId)
) ON [PRIMARY]

Add Column


ALTER table dbo.MyLOG1 add new1 char(1) null

Move Data(移動300000筆)


INSERT INTO MyLOG1 (agentname,LogDate)
SELECT agentname,LogDate FROM sourceLog 
where [LogId]>0 AND [LogId]<=300000

總共使用36191 KB，最後交易記錄檔大小53190KB。

由於邏輯交易記錄檔結尾已達實體記錄檔結尾，

且沒有執行交易記錄檔備份(沒有截斷並標示之前空間重用)，

所以發生實體交易記錄檔自動擴充行為(新增VLF)。

註:


VLF數量沒有固定。


VLF大小沒有固定。


VLF會從實體記錄檔最前面開始依順序寫入紀錄。

接下來執行備份資料庫和交易記錄檔後，看看備份檔案大小。

避免長時間交易(減少資料量)和uncommitted 交易

上面我們知道資料操作會產生大量的交易記錄，

所以下面針對資料操作我將使用迴圈來處理，

將每一批次資料量縮小(藉以縮短交易時間)，

並和大交易資料量所產生的交易記錄大小做個比較，

照理說短時間交易會產生較小的交易記錄量。

刪除資料庫並重新建立資料庫和來源資料表，

這次針對資料操作改成迴圈處理大資料量(同樣30萬筆)。


DECLARE @batchcount int,@startcount int
set @batchcount=2000
set @startcount=0
BEGIN try
    WHILE(@batchcount<=300000)
    begin        
        INSERT INTO MyLOG2 (agentname,LogDate)
        SELECT agentname,LogDate FROM SourceLOG 
        where [LogId]>@startcount AND [LogId]<=@batchcount        
        set @startcount= @batchcount
        set @batchcount=@batchcount+2000          
    end
end try
begin catch
 print error_message();
end catch