日志文件不停增长的原因以及解决的相关方法

--探讨日志文件不停增长的原因以及解决的相关方法


事务日志文件是SQL Server的另一个(还有一个为数据文件)重要组成部分。
每个数据库都有事务日志文件，用来记录所有事务以及每个事务对数据库
做的修改。为了提高SQL Server的整体性能，检索数据时将数据页读入缓
冲区高速缓存。数据的修改不是直接在磁盘上进行，而是修改高速缓存中
的页副本。直到数据库中出现检查点，或者必须将修改写入磁盘才能使用
缓冲区来容纳新页时，才会将这些修改写入磁盘。将修改后的数据页从高
速缓冲存储器写入磁盘的操作叫做刷新页。在高速缓存中修改，但尚未写
入磁盘的页称为“脏页”。

对缓冲区中的页进行修改时会在日志高速缓存中生成一条日志记录。
SQL Server具有防止在写入关联的日志记录前刷新脏页的逻辑。会确保日
志记录在提交事务时，或者在此之前一定已经被写入磁盘。

换句话说SQL Server对数据的insert,update,delete都只是在内存中完成
后，就提交事务。这些修改并不立刻同步到硬盘的数据页上。而SQL Server
又必须保证事务的一致性，哪怕是出现异常终止，内存中的修改没来得及
写入硬盘，下次重启的时候，能够恢复到一个事物一致的时间点。已经提
交的修改要在硬盘中的页面重新完成。为了做到这一点，必须依赖事务日
志


然而在SQL Server的使用过程中，会由于种种原因造成日志文件大小不停
的增长的现象。当日志文件达到最大限制(创建数据库是指定的日志文件
上限)，或者是把硬盘空间占满后，数据库将无法进行任何插入，修改，删
除的操作。

那么接下来我们来探讨出现这种问题的一些原因以及解决的相关方法

-->>TravyLee(物是人非事事休，欲语泪先流！)生成测试数据:
--创建测试数据库MyDb:
IF OBJECT_ID('MyDb') IS NOT NULL
DROP DATABASE MyDb
GO
CREATE DATABASE MyDb
GO

--日志文件到底有什么东西

这里我使用MyDb数据库，创建一个表TestLog,只包含一个int类型的字段，然后
将日志文件清空。接着执行DBCC LOG命令，找到日志文件中的最后一条记录

use MyDb
go
create table TestLog
(
	a int
)
go
checkpoint 
go
--backup log MyDb with truncate_only(此条命令在2008版本已经不再支持)
--2008的需要先备份数据库
BACKUP DATABASE [MyDb] 
TO  DISK = N'E:\MyDb_bak\MyDb_bak_20121113' 
GO
--然后再被分事务日志(备份事务日志后会自动清空之前的日志)
BACKUP LOG [MyDb] 
TO  DISK = N'E:\MyDb_bak\MyDb_log' 
GO

go
sp_helpdb--可以看到MyDb数据库的dbid
dbcc log(7,1)
go
/*
Current LSN             Operation                       Context                         Transaction ID LogBlockGeneration   Tag Bits Log Record Fixed Length Log Record Length Previous LSN            Flag Bits Log Reserve Description
----------------------- ------------------------------- ------------------------------- -------------- -------------------- -------- ----------------------- ----------------- ----------------------- --------- ----------- ------------------------

00000015:000000a0:0003  LOP_MODIFY_ROW                  LCX_BOOT_PAGE_CKPT              0000:00000000  0                    0x0000   62                      100               00000000:00000000:0000  0x0000    0           

(9 行受影响)

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

*/
--以上结果为执行dbcc log(7,1)返回的最后一条数据,一共返回了9条记录

接下来我对MyDb数据库的TestLog表进行insert操作

insert TestLog select 1
go
dbcc log(7,3)
go
/*
00000015:000000a5:0013	LOP_INSERT_ROWS	LCX_HEAP	0000:00000210	0	0x0000	62	104
*/
--我们可以在结果中找到和insert相关的记录（一共30条记录）

下面再插入一条记录:
insert TestLog select 100
go
dbcc log(7,3)
go
/*
00000015:000000a9:0001	LOP_BEGIN_XACT	LCX_NULL	0000:00000213	0	0x0000	64
00000015:000000a9:0002	LOP_INSERT_ROWS	LCX_HEAP	0000:00000213	0	0x0000	62
00000015:000000a9:0003	LOP_COMMIT_XACT	LCX_NULL	0000:00000213	0	0x0000	48
*/
--新的和insert相关的记录 从这些记录中我们可以找到刚刚的insert操作的事务，开始时间，结束时间，刚才连接的SPID等

下面执行一下更新操作:

update TestLog
set a=2
go
dbcc log(7,3)
go
/*
00000015:000000aa:0001	LOP_BEGIN_XACT	LCX_NULL	0000:00000214	0	0x0000	64	88
00000015:000000aa:0002	LOP_MODIFY_ROW	LCX_HEAP	0000:00000214	0	0x0000	62	100
00000015:000000aa:0003	LOP_MODIFY_ROW	LCX_HEAP	0000:00000214	0	0x0000	62	100
00000015:000000aa:0004	LOP_COMMIT_XACT	LCX_NULL	0000:00000214	0	0x0000	48	52
*/
--这次出现了和update相关的四条记录


对事务日志进行分析可以发现:

1,日志记录的是数据的变化，而不是用户发过来的操作类型

2,每条记录都有唯一的LSN编号，并且记录它属于的事务编号

3,日志记录的行数和实际发生变化的数据量有关系

4,日志记录事务发生的时间  但不保证记录发起这个事务的用
  户名，更不记录发起者的程序名称

5,SQL Server能够从日志文件里读到数据修改前的值和修改后
  的值。但是对于管理者来讲，直接从日志里面是很难了解其
  修改过程的。


日志文件增长的原因

SQL Server会为所有的修改记录日志。SQL Server也设计了相应的
机制，会定期日志文件中不再需要的日志。

如果日志文件里的需要的记录越来越多了，那么就会出现日志文件
不停地增长的现象。通常的原因有以下几个:

1,数据库恢复模式不是简单模式,但是没有安排日志备份
	
	对于非简单模式的数据库,只有做完日志备份后记录才会被截断
	做完整备份和差异备份都不会起这个作用
	
2,数据库上有一个长时间没有被提交的事务

3,数据库上有一个很大的事务正在运行

4,数据库的复制或者镜像出了异常

要避免日志文件不停的增长,那么就要想办法避免上面这些情况的发生

那么如何处避免呢?

对于一个最近不会去日志备份的数据库,设置成为简单恢复即可
如果数据库设计成了完整恢复模式,那就一定要定期安排定期做
日志备份.如果复制和镜像任务出了问题,需要及时解决.如果一
时找不到解决办法,建议暂时拆除复制或镜像,以防止日志记录越
积越多.在程序设计的时候也需要避免事务时间过长,不宜用一个
事物做太多的操作.如果数据库晚上或者周末会做一些维护工作
例如历史数据清洗,数据导入导出,索引重建等等.这是需要为他
们预留出足够的空间,并且在做完之后及时备份.

最后来说说如何定位造成日志增长的原因

--STEP ONE 检查日志现在使用情况和数据库状态:
DBCC SQLPERF(LOGSPACE)
GO
/*
MyDb	0.8046875	76.57767	0
*/
SELECT
	NAME,
	RECOVERY_MODEL_DESC,
	LOG_REUSE_WAIT,
	LOG_REUSE_WAIT_DESC
FROM
	sys.databases
	
GO
/*
MyDb	FULL	2	LOG_BACKUP
*/
以上语句可以找出Log Space Used(%)很高的，这是我们需要
定位什么原因造成日志记录不能被清除掉

如果数据库的日志从用等待状态(LOG_REUSE_WAIT_DESC)
对应的值是LOG_BACKUP，那就说明SQL Server在等待着
日志备份，这时需要检查备份计划，如果存在不合适的
计划，需要及时更改备份策略。如果用户不期待做日志
备份，那就可以直接把恢复模式改成简单。

STEP TWO 检查最老的活动事务:

USE MyDb
GO
/*
这时打开另一个窗口  输入以下语句:
USE MyDb
GO
begin tran
select name,number into test from master..spt_values
*/
我们可以很清楚的看到这个事务并没有被提交


然后执行以下命令找到活动的事务相关的
信息(我们不确定这个事务是否是那个未提交的事务)

DBCC OPENTRAN
GO

/*
数据库 'MyDb' 的事务信息。

最早的活动事务:
    SPID (服务器进程 ID): 53
    UID (用户 ID): -1
    名称          : user_transaction
    LSN           : (21:424:30)
    开始时间    : 11 13 2012  3:55:18:230PM
    SID           : 0x01
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
*/

=--执行下面的语句:

select
	st.text,
	t2.*
from
	sys.dm_exec_sessions as t2,
	sys.dm_exec_connections as t1
cross apply
	sys.dm_exec_sql_text(t1.most_recent_sql_handle) as st
where
	t1.session_id=t2.session_id
	and t1.session_id>50
/*
begin tran  select name,number into test from master..spt_values	53	2012-11-13 15:55:15.550	SX-1	Microsoft SQL Server Management Studio - 查询	348	6	.Net SqlClient Data Provider	0x01	sa			sleeping	0x	15	2	18	18	2	2012-11-13 15:55:18.230	2012-11-13 15:55:18.247	0	7	116	1	2147483647	简体中文	ymd	7	1	1	1	0	1	1	1	1	2	-1	0	2506	0	0x01	sa	NULL	NULL	NULL	2
*/

这时我们可以从结果中找到该SPID (服务器进程 ID): 53
下的未提交的活动的事务,而且通过返回的语句可以很清楚的
知道是该进程中的哪些语句执行的

这时我们只需杀死这个进程即可:

KILL 53

DBCC OPENTRAN
GO
/*
没有处于打开状态的活动事务。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

*/