由于某个事务长期不提交，导致日志不能被截断，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) |
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。说明事务已提交，事务日志被截断，从而可以释放磁盘空间。