SQLServer数据库中开启CDC导致事务日志空间被占满的原因

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),希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对我们网站的支持!

(0)

相关推荐

  • 深入分析MSSQL数据库中事务隔离级别和锁机制

    锁机制 NOLOCK和READPAST的区别. 1.       开启一个事务执行插入数据的操作. BEGIN TRAN t INSERT INTO Customer SELECT 'a','a' 2.       执行一条查询语句. SELECT * FROM Customer WITH (NOLOCK) 结果中显示"a"和"a".当1中事务回滚后,那么a将成为脏数据.(注:1中的事务未提交) .NOLOCK表明没有对数据表添加共享锁以阻止其它事务对数据表数据的修

  • 为mysql数据库添加添加事务处理的方法

    语句如下:alter table tableName engine=InnoDB; 用到的表 复制代码 代码如下: CREATE TABLE IF NOT EXISTS `test` ( `id` int(10) NOT NULL auto_increment, `websitename` varchar(200) character set utf8 NOT NULL, `websiteurl` varchar(200) character set utf8 NOT NULL, PRIMARY

  • ORACLE数据库事务隔离级别介绍

    两个并发事务同时访问数据库表相同的行时,可能存在以下三个问题: 1.幻想读:事务T1读取一条指定where条件的语句,返回结果集.此时事务T2插入一行新记录,恰好满足T1的where条件.然后T1使用相同的条件再次查询,结果集中可以看到T2插入的记录,这条新纪录就是幻想. 2.不可重复读取:事务T1读取一行记录,紧接着事务T2修改了T1刚刚读取的记录,然后T1再次查询,发现与第一次读取的记录不同,这称为不可重复读. 3.脏读:事务T1更新了一行记录,还未提交所做的修改,这个T2读取了更新后的数据

  • Android开发中的数据库事务用法分析

    本文实例讲述了Android开发中的数据库事务用法.分享给大家供大家参考,具体如下: 在android应用程序开发中,在使用到数据库的时候,事务处理是非常重要的. 首先Android数据库操作(特别是写操作)是非常慢的,将所有操作打包成一个事务能大大提高处理速度. 其次是保证数据的一致性,让一个事务中的所有操作都成功执行,或者失败,或者所有操作回滚. 如果您喜欢使用其他平台(如PHP + MySQL),代码通常在一个功能强大的服务器上运行,一般不会被意外中止,但在android平台上,您将会因为

  • MySQL数据库事务隔离级别介绍(Transaction Isolation Level)

    数据库隔离级别有四种,应用<高性能mysql>一书中的说明: 然后说说修改事务隔离级别的方法: 1.全局修改,修改mysql.ini配置文件,在最后加上 复制代码 代码如下: #可选参数有:READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE. [mysqld] transaction-isolation = REPEATABLE-READ 这里全局默认是REPEATABLE-READ,其实MySQL本来默认也是这个级别

  • PHP入门教程之使用Mysqli操作数据库的方法(连接,查询,事务回滚等)

    本文实例讲述了PHP入门教程之使用Mysqli操作数据库的方法.分享给大家供大家参考,具体如下: Demo1.php <?php //使用 mysqli 对象操作数据库 //创建 mysqli 对象(资源句柄) $_mysqli = new mysqli(); //连接数据库 1.主机名(ip) 2.账户 3.密码 4.数据库 //mysqli_connect 函数 == $_mysqli -> connect(); $_mysqli -> connect('localhost','ro

  • MSSQL与Oracle数据库事务隔离级别与锁机制对比

    一,事务的4个基本特征 Atomic(原子性): 事务中包含的操作被看做一个逻辑单元,这个逻辑单元中的操作要 么全部成功,要么全部失败. Consistency(一致性): 只有合法的数据可以被写入数据库,否则事务应该将其回滚到最初 状态. Isolation(隔离性): 事务允许多个用户对同一个数据进行并发访问,而不破坏数据的正 确性和完整性.同时,并行事务的修改必须与其他并行事务的修改 相互独立. Durability(持久性): 事务结束后,事务处理的结果必须能够得到固化. 以上属于废话

  • SQLServer数据库中开启CDC导致事务日志空间被占满的原因

    SQLServer中开启CDC之后,在某些情况下会导致事务日志空间被占满的现象为: 在执行增删改语句(产生事务日志)的过程中提示,The transaction log for database '***' is full due to 'REPLICATION'(数据库"***"的事务日志已满,原因为"REPLICATION"). CDC以及复制的基本原理粗略地讲,对于日志的使用步骤如下: 1,每当基础表(开启了CDC或者replication的表)产生事务性操作

  • 详解SqlServer数据库中Substring函数的用法

    功能:返回字符.二进制.文本或图像表达式的一部分 语法:SUBSTRING ( expression, start, length ) 1.substring(操作的字符串,开始截取的位置,返回的字符个数) 例如: 从'abbccc'中返回'ccc',charindex函数用法(charindex(查找的字符串,被查找的字符串,开始查找的位置),例如查找'abbccc'中第一个'c'出现的位置,charindex('c','abbccc',1)) declare @str1 varchar(25

  • Spring如何在一个事务中开启另一个事务

    这篇文章主要介绍了Spring如何在一个事务中开启另一个事务,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下 spring使用@Transactional开启事务,而且该注解使用propagation属性来指定事务的传播级别 @Transactional(propagation =Propagation.REQUIRES_NEW) // 开启一个新事务 使用REQUIRES_NEW就会开启一个新的事务吗? 答案并不是. 请看下面的这个示例 imp

  • 在Yii2中使用Pjax导致Yii2内联脚本载入失败的原因分析

    当我用defunkt/jquery-pjax载入Yii2的ActiveForm时发生一个错误,正常情况下是 ActiveForm的两个js应该先载入,而实际情况是 typeError:JQuery(...).yiiActiveForm is not a function. 在github的issues对这个问题已经讨论并得到了解决. Pjax首先通过html()执行内联的<script>,然后才通过executeScriptTags()执行带着src的<script>,所以导致找不

  • 设置SQLServer数据库中某些表为只读的多种方法分享

    一般情况下会有几种情况需要你把数据库设为只读: 1. Insert,Update,Delete 触发器 2. Check 约束 和 Delete 触发器 3. 设置数据库为只读 4. 把表放到只读文件组中 5. 拒绝对象级别权限 6. 创建视图 在开始之前,先创建一个数据库及表作为示例: 复制代码 代码如下: create database MyDB create table tblEvents ( id int, logEvent varchar(1000) ) insert into tbl

  • SQLServer 数据库中如何保持数据一致性

    根据实现策略的不同,主要有快照复制.事务复制.合并复制等三种类型.这三种复制类型,各有各的特点,分别适用于不同的场合.一般来说,在考虑采用哪种复制类型比较合适的时候,主要考虑的是性能与数据同步的时间间隔.那么在什么情形下比较适用快照复制呢?笔者就跟大家来讨论一下这个话题. 为了在恰当的时候采用快照复制,数据库管理员首先需要知道快照复制的特点.快照复制是指将数据以特定时刻的瞬时状态转发,而不坚实对数据的更新.在发生同步时,将生成完整的快照并将其发送到订阅服务器.简单的说,快照复制就是每隔一段时间发

  • SQLSERVER数据库中的5173错误解决方法

    昨天同事给你我一个有问题的数据库,叫我修复一下因为客户那边需要这个数据库,这个数据库只有一个mdf文件和一个ldf文件, 当我附加数据库的时候报错,数据库是SQL2005 附上有损坏的数据库文件: 因为之前在论坛也遇到过,所以按照论坛的方法来解决,结果还是不行 把ldf文件移到别的地方,然后附加的时候使用下面SQL语句重建事务日志文件 我的数据库文件放在C:\Users\Administrator\Desktop\新建文件夹目录下 复制代码 代码如下: USE [master] GO CREAT

  • sqlserver数据库中的表、字段sql语句

    1.系统表sysobjects 在数据库中创建的每个对象(例如约束.默认值.日志.规则以及存储过程)都对应一行. 列名 数据类型 说明 name sysname 对象名 id int 对象标识号 xtype char(2) 对象类型.可以是以下对象类型之一: AF = 聚合函数 (CLR) C = CHECK 约束 D = 默认值或 DEFAULT 约束 F = FOREIGN KEY 约束 L = 日志 FN = 标量函数 FS = 程序集 (CLR) 标量函数 FT = 程序集 (CLR)

  • MySQL中开启和使用通用查询日志的实例教程

    开启通用查询日志 因为为了性能考虑,一般通用查询日志general log不会开启.slow log可以定位一些有性能问题的sql,而general log会记录所有的SQL. mysql5.0版本,如果要开启slow log.general log,需要重启,从MySQL5.1.6版开始,general query log和slow query log开始支持写到文件或者数据库表两种方式,并且日志的开启,输出方式的修改,都可以在Global级别动态修改. mysql>select versio

  • 随机提取Access/SqlServer数据库中的10条记录的SQL语句

    代码如下:本文相关代码如下:Access:select top n * from table order by rnd(id)'id为数据库的自动编号字段Sql Server:select top n * from table order by newid() 但在ASP+Access中,或许是因为缓存的原因,第一条SQL语句无法得到预期的结果,而VB+Access则可以.解决办法是改用如下SQL语句: 本文相关代码如下:RandomizesSqlTxt="Select top 10 * Fro

随机推荐