MSDB数据库太大的问题

MSDB 数据库优化

最新推荐文章于 2024-01-06 16:36:56 发布

转载最新推荐文章于 2024-01-06 16:36:56 发布 · 3.1k 阅读

本文针对 SQL Server 2000 中 msdbdata.mdf 文件迅速增长的问题，提供了详细的故障排查及解决方案，包括通过创建聚集索引来提高 sp_delete_backuphistory 的执行效率。

msdb一般都很小，如果你的msdb很大，请确认：
1.msdb中是否有用户的对象和数据，如果有，请迁移
2.Job History是否保存过多？如果是，考虑设置“作业历史记录日志最大大小”。推荐20000就足矣。可以使用如下代码：

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=20000
GO

复制代码

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;

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;

EXEC sp_delete_backuphistory '01/01/11';

3.建议定期执行sp_delete_backuphistory

附加

==========