日志文件不停增长的原因以及解决的相关方法
--探讨日志文件不停增长的原因以及解决的相关方法事务日志文件是SQL Server的另一个(还有一个为数据文件)重要组成部分。每个数据库都有事务日志文件，用来记录所有事务以及每个事务对数据库做的修改。为了提高SQL Server的整体性能，检索数据时将数据页读入缓冲区高速缓存。数据的修改不是直接在磁盘上进行，而是修改高速缓存中的页副本。直到数据库中出现检查点，或者必须将修改写入磁盘才能使用缓冲区来容纳新页时，才会将这些修改写入磁盘。将修改后的数据页从高速缓冲存储器写入磁盘的操作叫做刷新页。在高速缓存中修改，但尚未写入磁盘的页称为“脏页”。对缓冲区中的页进行修改时会在日志高速缓存中生成一条日志记录。SQL Server具有防止在写入关联的日志记录前刷新脏页的逻辑。会确保日志记录在提交事务时，或者在此之前一定已经被写入磁盘。换句话说SQL Server对数据的insert,update,delete都只是在内存中完成后，就提交事务。这些修改并不立刻同步到硬盘的数据页上。而SQL Server又必须保证事务的一致性，哪怕是出现异常终止，内存中的修改没来得及写入硬盘，下次重启的时候，能够恢复到一个事物一致的时间点。已经提交的修改要在硬盘中的页面重新完成。为了做到这一点，必须依赖事务日志然而在SQL Server的使用过程中，会由于种种原因造成日志文件大小不停的增长的现象。当日志文件达到最大限制(创建数据库是指定的日志文件上限)，或者是把硬盘空间占满后，数据库将无法进行任何插入，修改，删除的操作。那么接下来我们来探讨出现这种问题的一些原因以及解决的相关方法-->>TravyLee(物是人非事事休，欲语泪先流！)生成测试数据:--创建测试数据库MyDb:IF OBJECT_ID('MyDb') IS NOT NULLDROP DATABASE MyDbGOCREATE DATABASE MyDbGO--日志文件到底有什么东西这里我使用MyDb数据库，创建一个表TestLog,只包含一个int类型的字段，然后将日志文件清空。接着执行DBCC LOG命令，找到日志文件中的最后一条记录use MyDbgocreate table TestLog(a int)gocheckpoint 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' GOgosp_helpdb--可以看到MyDb数据库的dbiddbcc 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 1godbcc log(7,3)go/*00000015:000000a5:0013LOP_INSERT_ROWSLCX_HEAP0000:0000021000x000062104*/--我们可以在结果中找到和insert相关的记录（一共30条记录）下面再插入一条记录:insert TestLog select 100godbcc log(7,3)go/*00000015:000000a9:0001LOP_BEGIN_XACTLCX_NULL0000:0000021300x00006400000015:000000a9:0002LOP_INSERT_ROWSLCX_HEAP0000:0000021300x00006200000015:000000a9:0003LOP_COMMIT_XACTLCX_NULL0000:0000021300x000048*/--新的和insert相关的记录 从这些记录中我们可以找到刚刚的insert操作的事务，开始时间，结束时间，刚才连接的SPID等下面执行一下更新操作:update TestLogset a=2godbcc log(7,3)go/*00000015:000000aa:0001LOP_BEGIN_XACTLCX_NULL0000:0000021400x0000648800000015:000000aa:0002LOP_MODIFY_ROWLCX_HEAP0000:0000021400x00006210000000015:000000aa:0003LOP_MODIFY_ROWLCX_HEAP0000:0000021400x00006210000000015:000000aa:0004LOP_COMMIT_XACTLCX_NULL0000:0000021400x00004852*/--这次出现了和update相关的四条记录对事务日志进行分析可以发现:1,日志记录的是数据的变化，而不是用户发过来的操作类型2,每条记录都有唯一的LSN编号，并且记录它属于的事务编号3,日志记录的行数和实际发生变化的数据量有关系4,日志记录事务发生的时间 但不保证记录发起这个事务的用 户名，更不记录发起者的程序名称5,SQL Server能够从日志文件里读到数据修改前的值和修改后 的值。但是对于管理者来讲，直接从日志里面是很难了解其 修改过程的。日志文件增长的原因SQL Server会为所有的修改记录日志。SQL Server也设计了相应的机制，会定期日志文件中不再需要的日志。如果日志文件里的需要的记录越来越多了，那么就会出现日志文件不停地增长的现象。通常的原因有以下几个:1,数据库恢复模式不是简单模式,但是没有安排日志备份对于非简单模式的数据库,只有做完日志备份后记录才会被截断做完整备份和差异备份都不会起这个作用2,数据库上有一个长时间没有被提交的事务3,数据库上有一个很大的事务正在运行4,数据库的复制或者镜像出了异常要避免日志文件不停的增长,那么就要想办法避免上面这些情况的发生那么如何处避免呢?对于一个最近不会去日志备份的数据库,设置成为简单恢复即可如果数据库设计成了完整恢复模式,那就一定要定期安排定期做日志备份.如果复制和镜像任务出了问题,需要及时解决.如果一时找不到解决办法,建议暂时拆除复制或镜像,以防止日志记录越积越多.在程序设计的时候也需要避免事务时间过长,不宜用一个事物做太多的操作.如果数据库晚上或者周末会做一些维护工作例如历史数据清洗,数据导入导出,索引重建等等.这是需要为他们预留出足够的空间,并且在做完之后及时备份.最后来说说如何定位造成日志增长的原因--STEP ONE 检查日志现在使用情况和数据库状态:DBCC SQLPERF(LOGSPACE)GO/*MyDb0.804687576.577670*/SELECTNAME,RECOVERY_MODEL_DESC,LOG_REUSE_WAIT,LOG_REUSE_WAIT_DESCFROMsys.databasesGO/*MyDbFULL2LOG_BACKUP*/以上语句可以找出Log Space Used(%)很高的，这是我们需要定位什么原因造成日志记录不能被清除掉如果数据库的日志从用等待状态(LOG_REUSE_WAIT_DESC)对应的值是LOG_BACKUP，那就说明SQL Server在等待着日志备份，这时需要检查备份计划，如果存在不合适的计划，需要及时更改备份策略。如果用户不期待做日志备份，那就可以直接把恢复模式改成简单。STEP TWO 检查最老的活动事务:USE MyDbGO/*这时打开另一个窗口 输入以下语句:USE MyDbGObegin transelect name,number into test from master..spt_values*/我们可以很清楚的看到这个事务并没有被提交然后执行以下命令找到活动的事务相关的信息(我们不确定这个事务是否是那个未提交的事务)DBCC OPENTRANGO/*数据库 'MyDb' 的事务信息。最早的活动事务: SPID (服务器进程 ID): 53 UID (用户 ID): -1 名称 : user_transaction LSN : (21:424:30) 开始时间 : 11 13 2012 3:55:18:230PM SID : 0x01DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。*/=--执行下面的语句:selectst.text,t2.*fromsys.dm_exec_sessions as t2,sys.dm_exec_connections as t1cross applysys.dm_exec_sql_text(t1.most_recent_sql_handle) as stwheret1.session_id=t2.session_idand t1.session_id>50/*begin tran select name,number into test from master..spt_values532012-11-13 15:55:15.550SX-1Microsoft SQL Server Management Studio - 查询3486.Net SqlClient Data Provider0x01sasleeping0x152181822012-11-13 15:55:18.2302012-11-13 15:55:18.2470711612147483647简体中文ymd7111011112-10250600x01saNULLNULLNULL2*/这时我们可以从结果中找到该SPID (服务器进程 ID): 53下的未提交的活动的事务,而且通过返回的语句可以很清楚的知道是该进程中的哪些语句执行的这时我们只需杀死这个进程即可:KILL 53DBCC OPENTRANGO/*没有处于打开状态的活动事务。DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。*/