SQLServer資料庫中開啟CDC導致“事務日誌空間被占滿，原因為REPLICATION”的原因分析和解決辦法

本文出處：http://www.cnblogs.com/wy123/p/6646143.html SQLServer中開啟CDC之後，在某些情況下會導致事務日誌空間被占滿的現象為：在執行增刪改語句（產生事務日誌）的過程中提示，The transaction log for database '*** ...

本文出處：http://www.cnblogs.com/wy123/p/6646143.html

SQLServer中開啟CDC之後，在某些情況下會導致事務日誌空間被占滿的現象為：
在執行增刪改語句（產生事務日誌）的過程中提示，The transaction log for database '***' is full due to 'REPLICATION'(資料庫“***”的事務日誌已滿，原因為“REPLICATION”).

CDC以及複製的基本原理粗略地講，對於日誌的使用步驟如下：
　　1，每當基礎表（開啟了CDC或者replication的表）產生事務性操作（增刪改）之後，對應的事務日誌寫入日誌文件，
　　2，此時的日誌被狀態被標記為Replication，也即處於待複製狀態，這個活動狀態跟資料庫的還原模式無關，即便是簡單還原模式，
　　3，然後有後臺進程來讀取這個日誌，根據事務日誌的記憶體寫入目標表，
　　　　這個目標對於cdc來說是記錄數據變化的系統表，
　　　　對於replication來說是寫入distribution這個庫
　　4，步驟3完成之後，事務日誌被標記為正常狀態，如果是簡單還原模式，被後臺進程解析過的事務日誌被截斷，可以重用

如果上述中間的第三個步驟出現問題，也即後臺進程無法解析日誌後釋放可用的日誌空間，再次往資料庫中寫入操作，就會出現：資料庫“TestDB”的事務日誌已滿，原因為“REPLICATION”的情況

本文通過通過演示開啟CDC的情況下日誌空間被占滿的現象，以及對應的處理辦法

測試環境搭建

　　首先建立一個測試資料庫，

USE master
GO
CREATE DATABASE TestLogFull ON PRIMARY 
( 
    NAME = N'TestLogFull', 
    FILENAME = N'D:\DBFile\TestLogFull\TestLogFull.mdf' , 
    SIZE = 500MB , 
    MAXSIZE = UNLIMITED, 
    FILEGROWTH = 100MB 
)
LOG ON 
(
    NAME = N'TestLogFull_log', 
    FILENAME = N'D:\DBFile\TestLogFull\TestLogFull_Log.ldf' , 
    SIZE = 1MB , 
    MAXSIZE = 512MB 
)

　　這裡指定日誌文件的最大為512M，主要是為了演示日誌空間被占滿的現象

　　接著開啟新建一個表同時開啟CDC來測試

USE TestLogFull
--啟用CDC
EXECUTE sys.sp_cdc_enable_db;
GO

--創建一張測試表
create table test_cdc
(
    id int identity(1,1) primary key,
    name nvarchar(50),
    mail varchar(50),
    address nvarchar(50),
    lastupdatetime datetime
)

--對錶啟用CDC
EXEC sys.sp_cdc_enable_table 
    @source_schema            = 'dbo',
    @source_name              = 'test_cdc',
    @role_name                = 'cdc_admin',
    @capture_instance         = DEFAULT,
    @supports_net_changes     = 1,
    @index_name               = NULL,
    @filegroup_name           = DEFAULT

　　CDC開啟成功，開始測試日誌被占滿的情況

　　這裡演示對某些表開啟CDC的情況下日誌文件文件被占滿的情況

1，代理伺服器未啟動導致日誌空間被占滿

文中一開始提到的步驟3，對於CDC，進程就是SQL Server Agent中的cdc.***_capture作業或者複製代理作業來讀取日誌
如果SQL Server Agent在開啟了CDC或者複製之後被關閉，或者重啟伺服器之後SQL Server Agent沒有隨機自動啟動
就有可能造成步驟2中的日誌積壓，也就是記錄數據變化之後的事務日誌處於replication狀態，無法重用，導致沒有可以使用的日誌
致使發生操作資料庫的時候提示The transaction log for database '***' is full due to 'REPLICATION'.

　　這裡暫時關閉代理服務（僅僅是為了測試演示這一現象）

　　增刪改都可以產生事務日誌，這裡就演示insert數據的情況，做一個寫數據的SQL，往開啟了CDC的表中寫資料庫
　　在建庫的時候日誌文件有限製成了512M，因為這個表上開啟了CDC，寫數據這個過程會產生事務日誌，日誌有空空間限制
　　在寫入數據的過程中，一開始是沒有問題的，隨著數據的不斷寫入（Replication狀態的日誌不斷積壓），當日誌全部使用之後，下麵的報錯就會產生了

　　此時觀察事務日誌的使用情況，發現已經是完全使用了，

　　因為日誌空間被完全使用了，那麼觀察一下日誌的等待狀態，是Replication狀態

　　此時嘗試收縮也是無效的，因為日誌都是出於活動狀態，活動狀態的日誌是無法收縮的

　　可見，因為代理被關閉，讀取日誌的作業無法執行，造成日誌堵塞，那麼開啟代理來看看到底行不行？
　　開啟代理，查看CDC作業的執行情況，會發現，此時代理作業也不好使了，作業執行的時候並沒有成功，一樣提示說事務日誌已滿

　　此時觀察測試表的cdc目標表沒有任何數據，說明此時即便開啟了代理，cdc的作業依然沒有成功執行
　　那麼這裡為什麼CDC的代理作業也無法正常執行？
　　其實也不難理解，cdc的作業也是讀取事務日誌寫數據的，這中間也相當於有事務性操作，必須要藉助日誌來實現，而此時又沒有可用的日誌空間，
　　這個作業當然要失敗了。

　　那麼此時怎麼辦？
　既然是日誌堵塞了，就想辦法清理到這部分活動日誌，嘗試將事務日誌標記為已分發（雖然這裡是CDC，但是對於日誌的使用應該是跟複製一樣的）

　　根據本人的測試，在執行上面的語句，將複製的事物標記為已分發之後，
　　再次查看日誌使用率，發現還是100%，但是嘗試寫入數據的時候是成功的，再次寫入數據（一條即可）之後，日誌空間開始釋放，
　　應該是寫入時候的時候觸發被標記為已分發的日誌截斷，也就是將上面占用了100%的日誌空間釋放出來
　　然後再觀察日誌的使用率，發現如預期的，這部分日誌已被截斷，日誌空間不再是被完全占用了，日誌變成Nothing狀態（可重用）

　　這個測試說明，如果開啟了CDC，SQL Server代理沒有正常啟動或者對應的作業沒有正常啟動，日誌空間會隨著不斷產生的事物被占滿，導致資料庫無法進行寫入性操作　

　這裡是用過手動標記日誌為已分發的方式來釋放日誌的，這種情況下會導致cdc日誌斷裂的情況，也就是手動釋放的日誌無法傳遞到下游（cdc日誌表）
　　畢竟不是一個太好的辦法，下麵會說明另外一種辦法。

2，短時間內較大的事務性操作導致的日誌空間被占滿的情況

　對去上面所說的代理服務被關閉導致日誌堵塞的情況不同，這裡直接開啟代理服務，依舊拿著下麵的腳本往表中寫數據（比如實際業務中批量導入數據之類的）
　在寫入一段時間之後，依然出現了事務日誌被填滿的情況，這又是為什麼？

　還要從CDC的代理任務說起，這個代理的JOB雖然是連續執行的，但是因為上面寫數據的時候也是連續寫入的，也就是日誌是連續產生的，
　因為限制了日誌文件的大小（這裡為了方便演示，限製為512M），日誌文件有最大使用空間的限制。
　這裡可以認為是一個Session消耗日誌空間（Insert操作），一個進程解析日誌之後釋放日誌空間（代理作業），
　　但是消耗的速度要高於釋放的速度，一旦日誌空間被使用完，CDC的代理作業也無法完成，
　這樣就又造成了上面的情況：日誌空間被填滿，資料庫無法執行任何寫入操作，CDC作業也無法執行從而釋放可重用的日誌空間，
　上面是通過手動標記事務日誌的狀態來解決日誌文件被填滿的，
　直接手動標記日誌為已分發的做法是有點不合適的，
　一旦標記日誌狀態為已分發，接下來他就不會傳遞給CDC的系統表或者訂閱端了
　　這裡通過另外一種方法來解決此問題:既然當前日誌占滿了，就在添加一個日誌，註意新加日誌初始化的空間不要太小。
　（有興趣測試的盆友，這裡添加完日誌文件後註意耐心等待一兩分鐘）然後隨後的CDC作業會藉助新加的這個日誌空間會繼續執行

　　如果是當前邏輯此磁碟空間充足，也可以通過擴大事務日誌文件來實現

ALTER DATABASE TestLogFull
MODIFY FILE (
                NAME = N'TestLogFull_log', 
                FILENAME = N'D:\DBFile\TestLogFull\TestLogFull_log.ldf' , 
                SIZE = 600MB , 
                MAXSIZE = UNLIMITED, 
                FILEGROWTH = 100MB 
            )

　　此種情況說明，如果限制了日誌的大小（或者存儲日誌的磁碟空間不足），資料庫中開啟了CDC或者複製，
　　一旦數據出現大批量持續性寫入操作（增刪改），此時會出現SQL Server代理解析並釋放日誌的速度跟不上，也有可能造成日誌被占滿的情況

3，不增加日誌文件空間或者添加日誌文件情況下重啟SQLServer服務

　　這個辦法也是本人在重現這一現象並嘗試解決的時候試出來的，可行性不是太強，但還是說明一下，那就是重啟大法，同時重啟之後日誌文件也發生了一些有意思的變化
　　建庫的時候日誌文件限製為最大512M，同時沒有手動標記標記日誌為已分髮狀態，但是重啟SQLServer服務之後，如果存放日誌的磁碟有空間，這個日誌會自動擴充一部分
　　然後有了這部分擴充出來的日誌，代理job就可以解析Replication狀態的日誌（之後）就可以釋放日誌空間了（需要一段時間來解析並釋放日誌，根據待複製的日誌量有關）
　　下圖可以明顯看到，日誌限製為512MB，但是初始化為556MB，明顯大過最大日誌大小，這個是歸功於重啟SQLServer服務的結果

　　一下是在SQL Server 2014 SP2版本下測試的現象，

　如果是SQL Server 2014（非SP2補丁版），開啟CDC的方式占滿日誌則不會出現如下的情況，也就是說重啟有日誌並不會自動擴充一部分，
　我也是醉了，驗證個東西真不容易，這些小細節跟補丁版本也有關係，不過這種偏門的方法不能作為經驗！

總結：

　　當開啟了CDC之後，在相關表上的變化會寫入事務日誌（日誌狀態為Replication狀態），
　　代理任務會解析日誌，解析完日之後標記日誌為可重建狀態（如果是簡單還原模式，是可重用，如果是完整還原模式，日誌備份也無法截斷Replication狀態的日誌），
　　這種狀態下如果限制了日誌的最大大小比較小，或者沒有限制，存儲日誌的磁碟空間不足，
　　在大批量寫入數據（增刪改）的時候，有可能產生的日誌占滿日誌文件的情況，
　　會導致釋放日誌的代理作業無法進行，代理作業無法進行又無法釋放日誌，仿佛是死迴圈。
　　此時要麼新增日誌文件或者增加日誌文件的最大大小，要麼通過執行系統存儲過程sp_repldone來標記事務為已分發（標記事務日誌可重用）來解決這一問題。