求助：如何解决数据库日志的异常增大？

我的SQL Server数据库为WEB应用，每天数据更新量并不是很大，平时数据库日志文件每天增长幅度平均一天为1M左右吧。

但是最近这几天发现数据库日志文件每日增长速度很快，每天平均增长近100M，但是实际数据更新与往常都是大致相同，由于近期内快到年关了，实际数据访问更新量可能比平时还要少些。但是奇怪的是数据库库日志增长非常迅速，以致于我不得不至少两天内必须登录一次进行手动截断事务日志的操作(注：数据库的自动收缩功能是启用的)。

同时还发现一个问题，就是主库文件(mdf文件)的大小和修改日期几乎不变化，倒是日志文件的修改日期基本上与服务器当前时间保持一致。这让我很怀疑所有的数据都保存在内存中并没有写入到主库文件当中。只有当我在重启动服务器完成后，再检查主库文件，才可以看到其文件修改日期已经更新成了重启前的时间。

（注：如果提供类似手动截断日志这种办法来进行收缩日志的）
请大家帮我分析一下到底是什么问题？谢谢

17 个解决方案

#1

清空日志
DUMP TRANSACTION 库名 WITH NO_LOG

截断事务日志
BACKUP LOG 数据库名 WITH NO_LOG

收缩数据库
DBCC SHRINKDATABASE(数据库名)

#2

壓一下
dbcc shrinkdatabase(庫日志,大小)
如
dbcc shrinkdatabase(test_log,3)

#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)

用第三方工具

Log Explorer 3.2

http://www.ttdown.com/SoftDown.asp?ID=14562
http://www.yiii.net/app/servlet/net.yiii.club.DownloadServlet?Information_Id=I00023471
解压缩密码 www.heibai.net
http://www.ttdown.com/softview_8647.htm

Log Explorer for SQL Server v4.0.2
http://js.fixdown.com/soft/8324.htm

注册机产生的是注册码，是两个

用解压缩密码解开后，压缩包里也有一个注册机的

打开log explorer file=>attach log file->选择服务器和登陆方式->connect->
选择数据库->attach->左面对话框中browse->view log->就可以看到log记录了
点击“View DDL Commands”里面就有很多drop table 命令
点击下面的“undo”按钮是生成表结构的语句（create table ....）
点击下面的“Salvage”按钮是生成插入语句的（insert into ...values....）
（以上lynx1111提供）

想恢复的话: 右键log记录 undo transation->选择保存文件名和路径->然后打开该文件到查询分析器里执行
T-sql代码就可以了

例如如果log是delete table where ...的话,生成的文件代码就是insert table ....

使用经验总结帖:
http://community.csdn.net/Expert/topic/2954/2954818.xml?temp=.9148676

#4

感谢楼上的加复。上述办法我知道，这正是我上服务器进行手动清空日志的办法。但这些解决不了我所提出的问题。

我的问题在于，日志大小增长速度仍然比较迅猛，并没有因为这样操作暂时变小后就放缓增长速度。如果频频做这样的操作太麻烦了，我想知道是哪些原因导致了我所说的问题，并提出一个解决这个问题的办法，让日志增长速度恢复到正常水平。

#5

日志增长快肯定是对数据库操作频繁引起的,这种操作或是你或是其他用户,建议查一下用户的操作过程,所谓更新很少可能是从表面看的,比如只更新了一条记录,从1改为2,但也可能是从1改为2,再改为1,再改为2,...,最后结果从表面看是一样的,日志却会大很多.另外,这些更新会不会是删除再插入呢?

#6

#7

To yesyesyes ：

你说的有一定道理。但是有几个疑问点我一直解释不了。
一、这个系统运行有一年多了，我可以确定近期内web访问代码也没有做任何的改变，数据库访问逻辑没有变化，以前维护日志没有出现这样的情况，为什么近期出现了呢？
二、如果这种日志增长变化是突发性、偶然性的我可以理解。但是近期内因年关到了访问量根本没有平时大，日志却增长如此之快，而且每天增长量似乎比较稳定，在100M上下的样子，
三、主库文件(MDF)大小和文件修改日期不变化，这种状态可以持续好几天，直到重启动服务器后，大小才会变化，日期也会改成重启动时的时间。可以理解为这么长时间的数据修改基本是在内存中进行的，没有主动写入主库文件当中，除非强制。为什么会这样。

这些让我困惑。而且我想这些问题如果解决不好的话，这种现象会一直持续下去。

#8

给分！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT

USE Marias -- 要操作的数据库名
SELECT @LogicalFileName = 'Marias_log', -- 日志文件名
@MaxMinutes = 10, -- 更新的最长分钟数
@NewSize = 100 -- 你想设定的日志文件的大小(M)

-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)

DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'

DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log')
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF

#9

给分不是问题，如问题解决，我可以再加五十分结贴。 ycheng可以留个联系方式嘛？

#10

#11

问题仍然没有解决。我现在临时的办法只是设定了一个周期性任务，每周晚间周期性地定时清空日志，备份数据库。如果此问题在本周一之前仍无妥善解决办法，我只好结贴了。

另外，请大家帮我解决我的问题前最好先看清楚我的问题说明，两个核心问题必须解释：一、为什么主库文件只有在重启动后才会被强制读写更新；二、为什么日志异常增大。这两个问题应该是存在关联的。大家不必再跟贴说明如何备份日志和截断日志的操作办法了。

同时感谢帮我顶贴者

#12

#13

偶和楼主有同样的困扰，日志也是每天狂涨，只能定期手工清理
关注此帖

#14

我觉得是不是安全性出了问题,其实你可以用SQL的监视器,查看一下,在一个时间段内,对数据库有哪些操作,这些操作是不是合理,合法,为什么没有提交,然后就能有针对的解决了!

#15

用监视器

#16

自动收缩

#17

将故障恢复模型改为简单，日志文件就永远不会增长了，当然这样做如果你想对日志进行回滚就不大好了

#1