2008年11月14日

sql server 2005数据库日志无法收缩

作者 非鱼

数据库出问题的过程:数据库体积很大,分两个文件,共40多G,没有启动完整日志,用的是简单日志。经过几次非法关机重启,数据库状态被标为可疑,无法使用。为了尽量减少损失,没有采取还原备份的方式,想办法恢复此数据库。按照下面的步骤解决了状态为可疑的问题:

USE MASTER
GO
SP_CONFIGURE ‘ALLOW UPDATES’,1 RECONFIGURE WITH OVERRIDE
GO
ALTER DATABASE MyDB SET EMERGENCY
GO
sp_dboption ‘MyDB’, ‘single user’, ‘true’
GO
DBCC CHECKDB(‘MyDB’,’REPAIR_ALLOW_DATA_LOSS’)
GO
ALTER DATABASE MyDB SET ONLINE
GO
sp_configure ‘allow updates’, 0 reconfigure with override
GO
sp_dboption ‘MyDB’, ‘single user’, ‘false’
GO

数据库可以用了,但是操作系统日志里频繁的出现一些sql server的错误日志,类似于

伺服器: 訊息 7105,層級 22,狀態 6,行 1

text、ntext、或是 image 節點的分頁 (1:62855),插槽 19 不存在。

这种(原文找不到了,这个是网上找的)。后来对几个大的表进行了单独的dbcc checktable修复操作,显示修复了一些错误,数据库使用起来比较正常了。

为了防止这种意外再次发生,把数据库日志改成了完整。后来发现日志膨胀的速度非常快,到了20G的时候,想收缩一下日志,结果失败。

无论是把日志改为简单,还是执行BACKUP LOG with no_log,再去收缩日志仍然显示可用空间为0,收缩没有任何效果。

最后日志一直增长到60G,正好这个时候换了台新服务器,于是只复制了数据库文件过来,日志文件没有复制过来,再附加,可以自动生成一个新的日志文件(前提是必须在原服务器上分离,直接停止数据库服务是不行的,附加的时候会说数据库没有正常关闭,必须要原来的日志文件才能附加。因为这个,40几G的数据库在两台机器之间拷了两回。)

在这个新的日志增长到6G的时候,再尝试截断后收缩,结果还是说里面的可用空间为0,无法收缩。后来再博客园朋友的提示下,用DBCC UPDATEUSAGE命令修复数据库的行记录数,修复了一些错误,但是还是无法收缩。根据该网友的提示,换了一些英文关键词在Google上搜索,后来找到这样一个函数:DBCC OPENTRAN(dbname),显示

已复制的事务信息:
最早的分布式 LSN : (0:0:0)
最早的非分布式 LSN : (1051867:2025:1)
DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

而在其它正常的数据库上运行这个命令,则没有前面三行东西。那么,问题应该是在这里了。再加上这些关键词,最后,终于找到一个跟我差不多原因的一篇帖子:http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3757111&SiteID=1&pageid=0

这篇文章的最后给出了微软MSDN的一个链接,里面提到了一个命令:sp_removedbreplication。通常如果你设置过数据库复制或发布,而后来设置失败并没有启用,可能会导致这个问题,你看不到跟复制有关的内容,但是在数据库里却存在这样的东西,于是日志被它堵住了,这成了一个永远无法完成的命令,所以后续的日志都无法截断。执行了这个命令以后,强制清除了复制内容。虽然我的数据库从来没有手工设置过复制相关的东西,但是不知道修复数据库的时候改错了什么东西,造成了这样的后果。执行了这个函数以后,再去收缩日志,可用空间变成了99%,成功将日志文件收缩到10M大小。

一个困扰了一个月的问题终于解决了。