共計(jì) 4409 個(gè)字符，預(yù)計(jì)需要花費(fèi) 12 分鐘才能閱讀完成。

行業(yè)資訊    
數(shù)據(jù)庫    
SQLServer 數(shù)據(jù)庫中開啟 CDC 導(dǎo)致事務(wù)日志空間被占滿的原因是什么

SQLServer 數(shù)據(jù)庫中開啟 CDC 導(dǎo)致事務(wù)日志空間被占滿的原因是什么，很多新手對此不是很清楚，為了幫助大家解決這個(gè)難題，下面丸趣 TV 小編將為大家詳細(xì)講解，有這方面需求的人可以來學(xué)習(xí)下，希望你能有所收獲。

SQLServer 中開啟 CDC 之后，在某些情況下會(huì)導(dǎo)致事務(wù)日志空間被占滿的現(xiàn)象為：

在執(zhí)行增刪改語句（產(chǎn)生事務(wù)日志）的過程中提示，The transaction log for database *** is full due to REPLICATION (數(shù)據(jù)庫“***”的事務(wù)日志已滿，原因?yàn)椤癛EPLICATION”).

CDC 以及復(fù)制的基本原理粗略地講，對于日志的使用步驟如下：

1，每當(dāng)基礎(chǔ)表（開啟了 CDC 或者 replication 的表）產(chǎn)生事務(wù)性操作（增刪改）之后，對應(yīng)的事務(wù)日志寫入日志文件，

2，此時(shí)的日志被狀態(tài)被標(biāo)記為 Replication，也即處于待復(fù)制狀態(tài)，這個(gè)活動(dòng)狀態(tài)跟數(shù)據(jù)庫的還原模式無關(guān)，即便是簡單還原模式，

3，然后有后臺進(jìn)程來讀取這個(gè)日志，根據(jù)事務(wù)日志的內(nèi)存寫入目標(biāo)表，

這個(gè)目標(biāo)對于 cdc 來說是記錄數(shù)據(jù)變化的系統(tǒng)表，

對于 replication 來說是寫入 distribution 這個(gè)庫

4，步驟 3 完成之后，事務(wù)日志被標(biāo)記為正常狀態(tài)，如果是簡單還原模式，被后臺進(jìn)程解析過的事務(wù)日志被截?cái)啵梢灾赜萌绻鲜鲋虚g的第三個(gè)步驟出現(xiàn)問題，也即后臺進(jìn)程無法解析日志后釋放可用的日志空間，再次往數(shù)據(jù)庫中寫入操作，就會(huì)出現(xiàn)：數(shù)據(jù)庫“TestDB”的事務(wù)日志已滿，原因?yàn)椤癛EPLICATION”的情況

本文通過通過演示開啟 CDC 的情況下日志空間被占滿的現(xiàn)象，以及對應(yīng)的處理辦法

測試環(huán)境搭建

首先建立一個(gè)測試數(shù)據(jù)庫，

USE masterGOCREATE 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，主要是為了演示日志空間被占滿的現(xiàn)象

接著開啟新建一個(gè)表同時(shí)開啟 CDC 來測試

USE TestLogFull-- 啟用 CDCEXECUTE sys.sp_cdc_enable_db;GO-- 創(chuàng)建一張測試表 create table test_cdc( id int identity(1,1) primary key, name nvarchar(50), mail varchar(50), address nvarchar(50), lastupdatetime datetime)-- 對表啟用 CDCEXEC 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，代理服務(wù)器未啟動(dòng)導(dǎo)致日志空間被占滿

文中一開始提到的步驟 3，對于 CDC，進(jìn)程就是 SQL Server Agent 中的 cdc.***_capture 作業(yè)或者復(fù)制代理作業(yè)來讀取日志如果 SQL Server Agent 在開啟了 CDC 或者復(fù)制之后被關(guān)閉，或者重啟服務(wù)器之后 SQL Server Agent 沒有隨機(jī)自動(dòng)啟動(dòng)就有可能造成步驟 2 中的日志積壓，也就是記錄數(shù)據(jù)變化之后的事務(wù)日志處于 replication 狀態(tài)，無法重用，導(dǎo)致沒有可以使用的日志致使發(fā)生操作數(shù)據(jù)庫的時(shí)候提示 The transaction log for database *** is full due to REPLICATION .

這里暫時(shí)關(guān)閉代理服務(wù)（僅僅是為了測試演示這一現(xiàn)象）

增刪改都可以產(chǎn)生事務(wù)日志，這里就演示 insert 數(shù)據(jù)的情況，做一個(gè)寫數(shù)據(jù)的 SQL，往開啟了 CDC 的表中寫數(shù)據(jù)庫 在建庫的時(shí)候日志文件有限制成了 512M，因?yàn)檫@個(gè)表上開啟了 CDC，寫數(shù)據(jù)這個(gè)過程會(huì)產(chǎn)生事務(wù)日志，日志有空空間限制在寫入數(shù)據(jù)的過程中，一開始是沒有問題的，隨著數(shù)據(jù)的不斷寫入（Replication 狀態(tài)的日志不斷積壓），當(dāng)日志全部使用之后，下面的報(bào)錯(cuò)就會(huì)產(chǎn)生了

此時(shí)觀察事務(wù)日志的使用情況，發(fā)現(xiàn)已經(jīng)是完全使用了，

因?yàn)槿罩究臻g被完全使用了，那么觀察一下日志的等待狀態(tài)，是 Replication 狀態(tài)

此時(shí)嘗試收縮也是無效的，因?yàn)槿罩径际浅鲇诨顒?dòng)狀態(tài)，活動(dòng)狀態(tài)的日志是無法收縮的

可見，因?yàn)榇肀魂P(guān)閉，讀取日志的作業(yè)無法執(zhí)行，造成日志堵塞，那么開啟代理來看看到底行不行？開啟代理，查看 CDC 作業(yè)的執(zhí)行情況，會(huì)發(fā)現(xiàn)，此時(shí)代理作業(yè)也不好使了，作業(yè)執(zhí)行的時(shí)候并沒有成功，一樣提示說事務(wù)日志已滿

此時(shí)觀察測試表的 cdc 目標(biāo)表沒有任何數(shù)據(jù)，說明此時(shí)即便開啟了代理，cdc 的作業(yè)依然沒有成功執(zhí)行 那么這里為什么 CDC 的代理作業(yè)也無法正常執(zhí)行？

其實(shí)也不難理解，cdc 的作業(yè)也是讀取事務(wù)日志寫數(shù)據(jù)的，這中間也相當(dāng)于有事務(wù)性操作，必須要借助日志來實(shí)現(xiàn)，而此時(shí)又沒有可用的日志空間，

這個(gè)作業(yè)當(dāng)然要失敗了。

那么此時(shí)怎么辦？

既然是日志堵塞了，就想辦法清理到這部分活動(dòng)日志，嘗試將事務(wù)日志標(biāo)記為已分發(fā)（雖然這里是 CDC，但是對于日志的使用應(yīng)該是跟復(fù)制一樣的）

根據(jù)本人的測試，在執(zhí)行上面的語句，將復(fù)制的事物標(biāo)記為已分發(fā)之后，再次查看日志使用率，發(fā)現(xiàn)還是 100%，但是嘗試寫入數(shù)據(jù)的時(shí)候是成功的，再次寫入數(shù)據(jù)（一條即可）之后，日志空間開始釋放，應(yīng)該是寫入時(shí)候的時(shí)候觸發(fā)被標(biāo)記為已分發(fā)的日志截?cái)啵簿褪菍⑸厦嬲加昧?100% 的日志空間釋放出來然后再觀察日志的使用率，發(fā)現(xiàn)如預(yù)期的，這部分日志已被截?cái)啵罩究臻g不再是被完全占用了，日志變成 Nothing 狀態(tài)（可重用）

這個(gè)測試說明，如果開啟了 CDC，SQL Server 代理沒有正常啟動(dòng)或者對應(yīng)的作業(yè)沒有正常啟動(dòng)，日志空間會(huì)隨著不斷產(chǎn)生的事物被占滿，導(dǎo)致數(shù)據(jù)庫無法進(jìn)行寫入性操作

這里是用過手動(dòng)標(biāo)記日志為已分發(fā)的方式來釋放日志的，這種情況下會(huì)導(dǎo)致 cdc 日志斷裂的情況，也就是手動(dòng)釋放的日志無法傳遞到下游（cdc 日志表）

畢竟不是一個(gè)太好的辦法，下面會(huì)說明另外一種辦法。

2，短時(shí)間內(nèi)較大的事務(wù)性操作導(dǎo)致的日志空間被占滿的情況

對去上面所說的代理服務(wù)被關(guān)閉導(dǎo)致日志堵塞的情況不同，這里直接開啟代理服務(wù)，依舊拿著下面的腳本往表中寫數(shù)據(jù)（比如實(shí)際業(yè)務(wù)中批量導(dǎo)入數(shù)據(jù)之類的）

在寫入一段時(shí)間之后，依然出現(xiàn)了事務(wù)日志被填滿的情況，這又是為什么？

還要從 CDC 的代理任務(wù)說起，這個(gè)代理的 JOB 雖然是連續(xù)執(zhí)行的，但是因?yàn)樯厦鎸憯?shù)據(jù)的時(shí)候也是連續(xù)寫入的，也就是日志是連續(xù)產(chǎn)生的，

因?yàn)橄拗屏巳罩疚募拇笮。ㄟ@里為了方便演示，限制為 512M），日志文件有最大使用空間的限制。

這里可以認(rèn)為是一個(gè) Session 消耗日志空間（Insert 操作），一個(gè)進(jìn)程解析日志之后釋放日志空間（代理作業(yè)），

但是消耗的速度要高于釋放的速度，一旦日志空間被使用完，CDC 的代理作業(yè)也無法完成，

這樣就又造成了上面的情況：日志空間被填滿，數(shù)據(jù)庫無法執(zhí)行任何寫入操作，CDC 作業(yè)也無法執(zhí)行從而釋放可重用的日志空間，

上面是通過手動(dòng)標(biāo)記事務(wù)日志的狀態(tài)來解決日志文件被填滿的，

直接手動(dòng)標(biāo)記日志為已分發(fā)的做法是有點(diǎn)不合適的，

一旦標(biāo)記日志狀態(tài)為已分發(fā)，接下來他就不會(huì)傳遞給 CDC 的系統(tǒng)表或者訂閱端了

這里通過另外一種方法來解決此問題: 既然當(dāng)前日志占滿了，就在添加一個(gè)日志，注意新加日志初始化的空間不要太小。

（有興趣測試的盆友，這里添加完日志文件后注意耐心等待一兩分鐘）然后隨后的 CDC 作業(yè)會(huì)借助新加的這個(gè)日志空間會(huì)繼續(xù)執(zhí)行

此種情況說明，如果限制了日志的大小（或者存儲日志的磁盤空間不足），數(shù)據(jù)庫中開啟了 CDC 或者復(fù)制，

一旦數(shù)據(jù)出現(xiàn)大批量持續(xù)性寫入操作（增刪改），此時(shí)會(huì)出現(xiàn) SQL Server 代理解析并釋放日志的速度跟不上，也有可能造成日志被占滿的情況

3，不增加日志文件空間或者添加日志文件情況下重啟 SQLServer 服務(wù)

這個(gè)辦法也是本人在重現(xiàn)這一現(xiàn)象并嘗試解決的時(shí)候試出來的，可行性不是太強(qiáng)，但還是說明一下，那就是重啟大法，同時(shí)重啟之后日志文件也發(fā)生了一些有意思的變化

建庫的時(shí)候日志文件限制為最大 512M，同時(shí)沒有手動(dòng)標(biāo)記標(biāo)記日志為已分發(fā)狀態(tài)，但是重啟 SQLServer 服務(wù)之后，如果存放日志的磁盤有空間，這個(gè)日志會(huì)自動(dòng)擴(kuò)充一部分

然后有了這部分?jǐn)U充出來的日志，代理 job 就可以解析 Replication 狀態(tài)的日志（之后）就可以釋放日志空間了（需要一段時(shí)間來解析并釋放日志，根據(jù)待復(fù)制的日志量有關(guān)）

日志限制為 512MB，但是初始化為 556MB，明顯大過最大日志大小，這個(gè)是歸功于重啟 SQLServer 服務(wù)的結(jié)果

在 SQL Server 2014 SP2 版本下測試的現(xiàn)象，

如果是 SQL Server 2014（非 SP2 補(bǔ)丁版），開啟 CDC 的方式占滿日志則不會(huì)出現(xiàn)如下的情況，也就是說重啟有日志并不會(huì)自動(dòng)擴(kuò)充一部分，我也是醉了，驗(yàn)證個(gè)東西真不容易，這些小細(xì)節(jié)跟補(bǔ)丁版本也有關(guān)系，不過這種偏門的方法不能作為經(jīng)驗(yàn)！

當(dāng)開啟了 CDC 之后，在相關(guān)表上的變化會(huì)寫入事務(wù)日志（日志狀態(tài)為 Replication 狀態(tài)），代理任務(wù)會(huì)解析日志，解析完日之后標(biāo)記日志為可重建狀態(tài)（如果是簡單還原模式，是可重用，如果是完整還原模式，日志備份也無法截?cái)?Replication 狀態(tài)的日志），這種狀態(tài)下如果限制了日志的最大大小比較小，或者沒有限制，存儲日志的磁盤空間不足，在大批量寫入數(shù)據(jù)（增刪改）的時(shí)候，有可能產(chǎn)生的日志占滿日志文件的情況，會(huì)導(dǎo)致釋放日志的代理作業(yè)無法進(jìn)行，代理作業(yè)無法進(jìn)行又無法釋放日志，仿佛是死循環(huán)。

此時(shí)要么新增日志文件或者增加日志文件的最大大小，要么通過執(zhí)行系統(tǒng)存儲過程 sp_repldone 來標(biāo)記事務(wù)為已分發(fā)（標(biāo)記事務(wù)日志可重用）來解決這一問題。

看完上述內(nèi)容是否對您有幫助呢？如果還想對相關(guān)知識有進(jìn)一步的了解或閱讀更多相關(guān)文章，請關(guān)注丸趣 TV 行業(yè)資訊頻道，感謝您對丸趣 TV 的支持。