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

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

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

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

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

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

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

　　　　對(duì)于replication來(lái)說(shuō)是寫(xiě)入distribution這個(gè)庫(kù)

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

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

測(cè)試環(huán)境搭建

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

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

　　接著開(kāi)啟新建一個(gè)表同時(shí)開(kāi)啟CDC來(lái)測(cè)試

USE TestLogFull
--啟用CDC
EXECUTE sys.sp_cdc_enable_db;
GO
--創(chuàng)建一張測(cè)試表
create table test_cdc
(
  id int identity(1,1) primary key,
  name nvarchar(50),
  mail varchar(50),
  address nvarchar(50),
  lastupdatetime datetime
)
--對(duì)表啟用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開(kāi)啟成功，開(kāi)始測(cè)試日志被占滿(mǎn)的情況

　　這里演示對(duì)某些表開(kāi)啟CDC的情況下日志文件文件被占滿(mǎn)的情況

1，代理服務(wù)器未啟動(dòng)導(dǎo)致日志空間被占滿(mǎn)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

總結(jié)：

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

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

以上所述是小編給大家介紹的SQLServer數(shù)據(jù)庫(kù)中開(kāi)啟CDC導(dǎo)致"事務(wù)日志空間被占滿(mǎn)的原因分析和解決辦法(REPLICATION)，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！

最新評(píng)論