1.msdb中是否有用户的对象和数据,如果有,请迁移
2.Job History是否保存过多?如果是,考虑设置“作业历史记录日志最大大小”。推荐20000就足矣。可以使用如下代码: 3.如果是SQL Server 2008 Ent版本或者SQL Server 2008R2的Std版本,均可以使用Backup Compression功能,以压缩备份文件的大小。
4.考虑删除过时的备份文件,近保存近期(比如一周)的备份文件
问题描述
==========
SQL Server 2000中msdbdata.mdf文件迅速增长到20G
故障排查
========
MSDB数据库中restorefile和restorefilegroup表占用了较大空间(共约19G),存放在restorefile和resotrefilegroup的记录主要是对于该服务器所发生过的所有backup和restore操作的历史记录,可能原因如下:
1.可能是由于您之前发生过较多的备份和还原的操作
2.创建过备份和还原的维护计划
3.由于该SQL Server实例使用了较旧的时间,备份还原历史堆积导致
我们通过调用sp_delete_backuphistory来删除较早的备份和还原历史记录,但是发现经过长时间的进行都无法成功。
您通过对bakcupset,restorefile和restorefilegroup创建索引,试图能够成功调用sp_delete_backuphistory,但仍旧失败。
经过使用一下语句查询,发现resotrefile和restorefilegroup创建的是非聚集索引:
Select * from sysdindexes where id=OBJECT_ID(‘restorefile’)
Select * from sysdindexes where id=OBJECT_ID(‘restorefilegroup’)
返回结果indid=0,这说明创建的非聚集索引。
我们通过CREATE CLUSTERED INDEX语句为这两张大表创建聚集索引(聚集索引指索引中存放真实的数据,非聚集索引的索引中只存放索引键,不存放数据)。
创建成功后,再次执行sp_delete_backuphistory,可以成功执行,MSDB也可以成功收缩。
解决办法:
==========
1.使用一下语句为MSDB的几张大表创建聚集索引
USE msdb;
GO
CREATE CLUSTERED INDEX [backupset_s01] ON [dbo].[backupset] ([media_set_id])
CREATE CLUSTERED INDEX [restorefile_s01] ON [dbo].[restorefile] ([restore_history_id])
CREATE CLUSTERED INDEX [restorefilegroup_s01] ON [dbo].[restorefilegroup] ([restore_history_id])
2.执行一下语句删除备份和还原的历史记录
USE msdb;
GO
EXEC sp_delete_backuphistory '01/01/11';
3.建议定期执行sp_delete_backuphistory
附加
==========