事务日志文件 突然膨胀到4G,该如何办

前天看数据库日志还是 200M，
今天看时却是接近 4G, 为何变化如此之大?

有时一个月都是小文件,事务日志正常，
有时突然膨胀到4G，有一次系统竟干脆就挂起了,

为何SQL Server 有时出现这种反常的行为呢?

我这里采用的是 "完整模型","自动收缩",
今天查看后,虽然日志文件是4G, 但是内部占有的空间是 23M 

请指点一二, thanks ....

23 个解决方案

#1

用这个查看膨胀的日志是什么

#2

楼主看下发生这种反常行为时对数据库做了哪些操作，DBCC下数据库和表，有可能的话截断日志做收缩。

#3

可能是数据处理的方法有问题.
尽量使得事务内执行的命令减少,检查程序的逻辑性.减少数据处理对客户端程序的依赖

--压缩日志及数据库文件大小 

/*--特别注意 

请按步骤进行,未进行前面的步骤,请不要做后面的步骤 
否则可能损坏你的数据库. 


一般不建议做第4,6两步 
第4步不安全,有可能损坏数据库或丢失数据 
第6步如果日志达到上限,则以后的数据库处理会失败,在清理日志后才能恢复. 
--*/ 

--下面的所有库名都指你要处理的数据库的库名 

1.清空日志 
DUMP     TRANSACTION     库名     WITH     NO_LOG         

2.截断事务日志： 
BACKUP   LOG   库名   WITH   NO_LOG 

3.收缩数据库文件(如果不压缩,数据库的文件不会减小 
企业管理器--右键你要压缩的数据库--所有任务--收缩数据库--收缩文件 
--选择日志文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了 
--选择数据文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了 

也可以用SQL语句来完成 
--收缩数据库 
DBCC   SHRINKDATABASE(库名) 

--收缩指定数据文件,1是文件号,可以通过这个语句查询到:select   *   from   sysfiles 
DBCC   SHRINKFILE(1) 

4.为了最大化的缩小日志文件(如果是sql   7.0,这步只能在查询分析器中进行) 
a.分离数据库: 
企业管理器--服务器--数据库--右键--分离数据库 

b.在我的电脑中删除LOG文件 

c.附加数据库: 
企业管理器--服务器--数据库--右键--附加数据库 

此法将生成新的LOG，大小只有500多K 

或用代码：   
下面的示例分离   pubs，然后将   pubs   中的一个文件附加到当前服务器。 

a.分离 
EXEC   sp_detach_db   @dbname   =   '库名 ' 

b.删除日志文件 

c.再附加 
EXEC   sp_attach_single_file_db   @dbname   =   '库名 ',   
      @physname   =   'c:\Program   Files\Microsoft   SQL   Server\MSSQL\Data\库名.mdf ' 

5.为了以后能自动收缩,做如下设置: 
企业管理器--服务器--右键数据库--属性--选项--选择 "自动收缩 " 

--SQL语句设置方式: 
EXEC   sp_dboption   '库名 ',   'autoshrink ',   'TRUE '	

6.如果想以后不让它日志增长得太大 
企业管理器--服务器--右键数据库--属性--事务日志 
--将文件增长限制为xM(x是你允许的最大数据文件大小) 

--SQL语句的设置方式: 
alter   database   库名   modify   file(name=逻辑文件名,maxsize=20) 

#4

数据库日志突然增长到4G，意味着你有4G的数据发生了变化，也就是有大量的数据插入、更新或是删除操作等等；
"完整模式"需要有日志备份，这样才可以防止日志文件不断的增加；
"自动收缩"最好还是关了吧，解决不了日志增大问题。

#5

大量的数据插入。更新和删除会导致日志文件的突然增加

这个没什么好的办法避免

可以考虑用备份作业来做

完整备份，差异备份和日志备份都需要

#6

修改成简单恢复模式

#7

是否进行过大数据量的归档转移？

可以截断日志

backup log table_name with no_log
dbcc shrinkdatabase(table_name,truncateonly)

#8

引用 7 楼 yubofighting 的回复:

是否进行过大数据量的归档转移？

可以截断日志
SQL code

backup log table_name with no_log
dbcc shrinkdatabase(table_name,truncateonly)

这是2005的方法，2008里已经没有with no_log了

#9

引用 3 楼 mingpei0703 的回复:

可能是数据处理的方法有问题.
尽量使得事务内执行的命令减少,检查程序的逻辑性.减少数据处理对客户端程序的依赖



SQL code

--压缩日志及数据库文件大小 

/*--特别注意 

请按步骤进行,未进行前面的步骤,请不要做后面的步骤 
否则可能损坏你的数据库. 


一般不建议做第4,6两步 
第4步不安全,有可能损坏数据库或丢失数据 
第6步如果日志达……

+1

#10

如果有大量的数据插入、更新、删除等操作，日志文件的增加是必然，
可设置一个作业，先对数据库备份，再截断和收缩日志

#11

1) Convert the Recovery Model to Simple Recovery

If you are truncating the transaction logs, this means you are breaking the T-Log LSN (Log Sequence Numbers). This follows that if disaster comes, you would not be able to restore your T-Logs and there would be no option for you to do point in time recovery. If you are fine with this situation and there is nothing to worry, I suggest that you change your recovery model to Simple Recovery Model. This way, you will not have extra ordinary growth of your log file.

 

1 转换恢复模式到简单恢复模式

如果你正在切断日志，那意味着你正在打断T-LOG LSN （日志的序列号）。 而随后的如果有灾难到来，你将不能利用日志来恢复你的数据库。如果你认为这还不错，没有什么值得担心的，那我建议你改变你的恢复模式到简单恢复模式。这样你将不在有额外的日志增长。

2) Start Taking Transaction Log Backup

2 开始做 Transaction  日志备份

If your business does not support loss of data or requires having point in time recovery, you cannot afford anything less than Full Recovery Model. In Full Recovery Model, your transaction log will grow until you take a backup of it. You need to take the T-Log Backup at a regular interval. This way, your log would not grow beyond some limits. If you are taking an hourly T-Log backup, your T-Log would grow until one hour but after this the T-Log backup would truncate all the ‘committed’ transactions once you take it. Doing this would lead the size of the T-Log not to go down much, but it would rather be marked as empty for the next hour’s T-Log to populate.

 

如果不能接受丢失数据库，或者需要利用时间点来恢复数据， 而你需要不小于完整备份的方式。 在FULL恢复模式，你的日志的增长会在备份后做一个段落。 你需要做Tracnsaction log 的备份在FULL 备份的区间之间。 如果你选择了每小时来备份T-LOG的备份，你的日志增长会在做完T-LOG后做一个段落。 T-LOG备份将气短在COMMITED前提交的工作。 这样做将控制你的日志尺寸，并减小它，这样远胜于几个小时候你清空日志。

With this method, you can restore your database at Point of Time if a disaster ever happens at your server.

用这样的方法你可以恢复你的数据库在任意时间点，当灾难发生的时候。

Let us run an example to demonstrate this. In this case, I have done the following steps:

1.Create Sample Database in FULL RECOVERY Model                                创建FULL 恢复模式
2.Take Full Backup (full backup is must for taking subsequent backup)     第一次做一次FULL 备份
3.Repeat Following Operation               重复 日志备份并检测日志尺寸
1.Take Log Backup
2.Insert Some rows
3.Check the size of Log File
4.Clean Up                                                 日志被清空
After a short while, you will notice that the Log file (ldf) will stop increasing but the size of the backup will increase.

而后，你将注意LDF文件减小，停止增加，但备份的文件尺寸将增加

#12

数据库日志突然增长到4G，意味着你有4G的数据发生了变化，也就是有大量的数据插入、更新或是删除操作等等；
"完整模式"需要有日志备份，这样才可以防止日志文件不断的增加；
"自动收缩"最好还是关了吧，解决不了日志增大问题。
--------------------------------------------------------------------------------
4G 的变化数据,这个很有道理,
但是在很长的时间内,一个月内,也就是在好多系统运行周期之后,
日志才突然膨胀,这令人不可琢磨,
我这必须用完整模式,因为客户端很多,而且很重要,
不能因为某个小故障而导致数据无法恢复(系统自动做的那种),

"自动收缩"只是无奈之举,在碰到日志快速膨胀时,希望它能起点作用,

突然膨胀的原因是什么呢,如何捕获呢?

#13

防止日志文件变大的最简单办法就是:
设置三个小一些的日志文件,如每个50M,它就不会变大了,当然,保存的日志内容也就少了.

#14

引用 8 楼 guguda2008 的回复:

引用 7 楼 yubofighting 的回复:

是否进行过大数据量的归档转移？

可以截断日志
SQL code

backup log table_name with no_log
dbcc shrinkdatabase(table_name,truncateonly)

这是2005的方法，2008里已经没有with no_log了

08还不怎么熟悉，那08吧变成什么关键字了？

#15

引用 14 楼 yubofighting 的回复:

引用 8 楼 guguda2008 的回复:
引用 7 楼 yubofighting 的回复:

是否进行过大数据量的归档转移？

可以截断日志
SQL code

backup log table_name with no_log
dbcc shrinkdatabase(table_name,truncateonly)

这是2005的方法，2008里已经没有with n……

dbcc shrinkdatabase Table_Name  '就可以把日志清理掉

#16

那就不要自动收缩了。

#17

引用 16 楼 wxf163 的回复:

那就不要自动收缩了。

因为真实的原因没有找到,如果不自动收缩, 一旦日志爆满, 那可是巨大的灾难

#18

使用profiler ,跟踪 log file auto grow事件和tsql相关事件. 两个事件参照一下就能知道是什么原因导致了logfile异常增长.

#19

检查一下作业,做个trace看看是否有定期的大量DML操作？

#20

平时正常，有时异常，看异常的那段时间是否有事务未能执行（该事务含有DML操作），在一直重复提交，重复提交会有日志大量产生

#21

引用 19 楼 orochi_gao 的回复:

检查一下作业,做个trace看看是否有定期的大量DML操作？

trace 如何配置操作的? trace 的具体跟踪内容是什么?

#22

引用 20 楼 bindong 的回复:

平时正常，有时异常，看异常的那段时间是否有事务未能执行（该事务含有DML操作），在一直重复提交，重复提交会有日志大量产生

因为它是偶尔出现的,一出现就是4G, 这个捕作还是比较难的,需要具体可行的方法

#23

建议LZ不要自动收缩了，压缩下日志了

  

BACKUP   LOG   库名   WITH   NO_LOG