事务长期不提交导致日志不能截断

  由于某个事务长期不提交,导致日志不能被截断,ldf文件持续增长,却不能收缩。


一、搭建测试环境

1. 创建数据库,恢复模式为简单

CREATE DATABASE [db01]

ON  PRIMARY

( NAME = N'db01', FILENAME = N'C:\sqldata\db01.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'db01_log', FILENAME = N'C:\sqldata\db01_log.ldf' , SIZE = 1024KB , FILEGROWTH = 1024KB)

ALTER DATABASE [db01] SET RECOVERY SIMPLE


2. 创建表并插入一条新记录

USE db01

CREATE TABLE table1

(UserID int,pwd char(20),OtherInfo char(4100),modifydate datetime)

INSERT table1

VALUES ( 123,'456','this is the first record',getdate() )


3. 备份数据库

BACKUP DATABASE [db01]

TO  DISK = N'C:\Backup\db01.bak' WITH FORMAT, INIT


二、执行事务但不提交

1. 执行一个事务,但不提交

BEGIN TRAN T1

SELECT *  FROM table1 WITH (NOLOCK)

113242933.png


2. 从同一个服务器进程id(SPID)执行以下循环,反复修改数据,使ldf增长

BEGIN TRAN T2

DECLARE @i int

SET @i=0

WHILE @i<10000

BEGIN

UPDATE table1

SET pwd = cast(floor(rand()*100000) as varchar(20)),

  OtherInfo=cast(floor(rand()*100000) as char(4100)),

  modifydate = GETDATE()

WHERE UserID=123

SET @i=@i+1

END

COMMIT TRAN T2

说明:由于第一笔交易未完成,当后续事务从同一个连接执行,被视为嵌套事务。


3. 在新的进程中,尝试收缩日志文件

DBCC SHRINKFILE (N'db01_log' , 0, TRUNCATEONLY)

注:不能在事务内执行SHRINKFILE操作。


5. 查看数据库的磁盘使用空间

  此时,ldf文件为6MB。而这个数据库最初创建时ldf文件为1MB。上述实验,说明嵌套事务中,某一个事务未提交,将导致事务日志不能截断。


参考 http://support.microsoft.com/kb/295108


三、查找未提交的事务

执行DBCC OPENTRAN

DBCC OPENTRAN

返回的信息如下:

数据库 'db01' 的事务信息。


最早的活动事务:

   SPID (服务器进程 ID): 53

   UID (用户 ID): -1

   名称          : T1

   LSN           : (39:65:1)

   开始时间    : 11 27 2013  11:18:59:950AM

   SID           : 0x010500000000000515000000b9970467bf7ed144e62fd7bbf4010000

DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。


四、处理未提交的事务

1. 提交事务

COMMIT TRAN T1


2. 收缩日志文件

DBCC SHRINKFILE (N'db01_log' , 0, TRUNCATEONLY)


3. 使用DBCC OPENTRAN查看活动事务,结果如下

数据库 'db01' 的事务信息。


最早的活动事务:

   SPID (服务器进程 ID): 54

   UID (用户 ID): -1

   名称          : T1

   LSN           : (39:65:1)

   开始时间    : 11 27 2013  2:18:59:950PM

   SID           : 0x010500000000000515000000b9970467bf7ed144e62fd7bbf4010000

DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。


4. 查看ldf文件

  此时,ldf文件从6MB收缩到1MB。说明事务已提交,事务日志被截断,从而可以释放磁盘空间。


  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值