程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> SQLServer數據庫中開啟CDC導致事務日志空間被占滿的原因,sqlservercdc

SQLServer數據庫中開啟CDC導致事務日志空間被占滿的原因,sqlservercdc

編輯:更多數據庫知識

SQLServer數據庫中開啟CDC導致事務日志空間被占滿的原因,sqlservercdc


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作業會借助新加的這個日志空間會繼續執行

  此種情況說明,如果限制了日志的大小(或者存儲日志的磁盤空間不足),數據庫中開啟了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來標記事務為已分發(標記事務日志可重用)來解決這一問題。

以上所述是小編給大家介紹的SQLServer數據庫中開啟CDC導致"事務日志空間被占滿的原因分析和解決辦法(REPLICATION),希望對大家有所幫助,如果大家有任何疑問請給我留言,小編會及時回復大家的。在此也非常感謝大家對幫客之家網站的支持!

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved