SQLServer數(shù)據(jù)庫中開啟CDC因REPLICATION導致事務日志空間被占滿該怎么辦

本篇文章給大家分享的是有關SQLServer數(shù)據(jù)庫中開啟CDC因REPLICATION導致事務日志空間被占滿該怎么辦，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

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

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

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

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

2，此時的日志被狀態(tài)被標記為Replication，也即處于待復制狀態(tài)，這個活動狀態(tài)跟數(shù)據(jù)庫的還原模式無關，即便是簡單還原模式，

3，然后有后臺進程來讀取這個日志，根據(jù)事務日志的內存寫入目標表，

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

對于replication來說是寫入distribution這個庫

4，步驟3完成之后，事務日志被標記為正常狀態(tài)，如果是簡單還原模式，被后臺進程解析過的事務日志被截斷，可以重用

如果上述中間的第三個步驟出現(xiàn)問題，也即后臺進程無法解析日志后釋放可用的日志空間，再次往數(shù)據(jù)庫中寫入操作，就會出現(xiàn)：數(shù)據(jù)庫“TestDB”的事務日志已滿，原因為“REPLICATION”的情況

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

測試環(huán)境搭建

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

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

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

USE TestLogFull --啟用CDC EXECUTE 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 )  --對表啟用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作業(yè)或者復制代理作業(yè)來讀取日志

如果SQL Server Agent在開啟了CDC或者復制之后被關閉，或者重啟服務器之后SQL Server Agent沒有隨機自動啟動

就有可能造成步驟2中的日志積壓，也就是記錄數(shù)據(jù)變化之后的事務日志處于replication狀態(tài)，無法重用，導致沒有可以使用的日志

致使發(fā)生操作數(shù)據(jù)庫的時候提示The transaction log for database '***' is full due to  'REPLICATION'.

這里暫時關閉代理服務(僅僅是為了測試演示這一現(xiàn)象)

增刪改都可以產(chǎn)生事務日志，這里就演示insert數(shù)據(jù)的情況，做一個寫數(shù)據(jù)的SQL，往開啟了CDC的表中寫數(shù)據(jù)庫

在建庫的時候日志文件有限制成了512M，因為這個表上開啟了CDC，寫數(shù)據(jù)這個過程會產(chǎn)生事務日志，日志有空空間限制

在寫入數(shù)據(jù)的過程中，一開始是沒有問題的，隨著數(shù)據(jù)的不斷寫入(Replication狀態(tài)的日志不斷積壓)，當日志全部使用之后，下面的報錯就會產(chǎn)生了

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

因為日志空間被完全使用了，那么觀察一下日志的等待狀態(tài)，是Replication狀態(tài)

此時嘗試收縮也是無效的，因為日志都是出于活動狀態(tài)，活動狀態(tài)的日志是無法收縮的

可見，因為代理被關閉，讀取日志的作業(yè)無法執(zhí)行，造成日志堵塞，那么開啟代理來看看到底行不行?

開啟代理，查看CDC作業(yè)的執(zhí)行情況，會發(fā)現(xiàn)，此時代理作業(yè)也不好使了，作業(yè)執(zhí)行的時候并沒有成功，一樣提示說事務日志已滿

此時觀察測試表的cdc目標表沒有任何數(shù)據(jù)，說明此時即便開啟了代理，cdc的作業(yè)依然沒有成功執(zhí)行

那么這里為什么CDC的代理作業(yè)也無法正常執(zhí)行?

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

這個作業(yè)當然要失敗了。

那么此時怎么辦?

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

根據(jù)本人的測試，在執(zhí)行上面的語句，將復制的事物標記為已分發(fā)之后，

再次查看日志使用率，發(fā)現(xiàn)還是100%，但是嘗試寫入數(shù)據(jù)的時候是成功的，再次寫入數(shù)據(jù)(一條即可)之后，日志空間開始釋放，

應該是寫入時候的時候觸發(fā)被標記為已分發(fā)的日志截斷，也就是將上面占用了100%的日志空間釋放出來

然后再觀察日志的使用率，發(fā)現(xiàn)如預期的，這部分日志已被截斷，日志空間不再是被完全占用了，日志變成Nothing狀態(tài)(可重用)

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

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

畢竟不是一個太好的辦法，下面會說明另外一種辦法。

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

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

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

還要從CDC的代理任務說起，這個代理的JOB雖然是連續(xù)執(zhí)行的，但是因為上面寫數(shù)據(jù)的時候也是連續(xù)寫入的，也就是日志是連續(xù)產(chǎn)生的，

因為限制了日志文件的大小(這里為了方便演示，限制為512M)，日志文件有***使用空間的限制。

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

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

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

上面是通過手動標記事務日志的狀態(tài)來解決日志文件被填滿的，

直接手動標記日志為已分發(fā)的做法是有點不合適的，

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

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

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

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

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

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

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

建庫的時候日志文件限制為***512M，同時沒有手動標記標記日志為已分發(fā)狀態(tài)，但是重啟SQLServer服務之后，如果存放日志的磁盤有空間，這個日志會自動擴充一部分

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

下圖可以明顯看到，日志限制為512MB，但是初始化為556MB，明顯大過***日志大小，這個是歸功于重啟SQLServer服務的結果

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

如果是SQL Server 2014(非SP2補丁版)，開啟CDC的方式占滿日志則不會出現(xiàn)如下的情況，也就是說重啟有日志并不會自動擴充一部分，

當開啟了CDC之后，在相關表上的變化會寫入事務日志(日志狀態(tài)為Replication狀態(tài))，

代理任務會解析日志，解析完日之后標記日志為可重建狀態(tài)(如果是簡單還原模式，是可重用，如果是完整還原模式，日志備份也無法截斷Replication狀態(tài)的日志)，

這種狀態(tài)下如果限制了日志的***大小比較小，或者沒有限制，存儲日志的磁盤空間不足，

在大批量寫入數(shù)據(jù)(增刪改)的時候，有可能產(chǎn)生的日志占滿日志文件的情況，

會導致釋放日志的代理作業(yè)無法進行，代理作業(yè)無法進行又無法釋放日志，仿佛是死循環(huán)。

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

以上就是SQLServer數(shù)據(jù)庫中開啟CDC因REPLICATION導致事務日志空間被占滿該怎么辦，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業(yè)資訊頻道。