暂无图片
暂无图片
3
暂无图片
暂无图片
暂无图片

【译】SQL Server:包含表dbo.sysmaintplan_logdetail的msdb数据库太大

原创 沐言倾心 2022-04-13
1908

原文地址:https://blog.dbi-services.com/sql-server-msdb-too-big-with-the-table-dbo-sysmaintplan_logdetail/
原文作者:Stéphane Haby

今天,在一个新客户现场,我看到msdb数据文件超过4GB。有点太大了…

  • 为了查看是哪张表占用这么大的空间,我们可以在SSMS中选择msdb数据库,右键点击->报告(Reports)->标准报告(Standards Reports)->按"热门表"划分的磁盘使用情况(Disk usage by Top Tables)
    图片.png

  • 如上图可见,表dbo.sysmainplan_logdetail是大小为2.3GB且包含13205条记录的大表。

此表是通过SSMS中管理菜单中的维护计划向导生成的维护计划的日志。更多信息请点击这里:如何使用维护计划向导

在谷歌搜索如何清除此表之后,发现很多文章描述了对表进行的截断操作的方法。
我不确定这是不是能解决此问题的好方法…

  • 根据我的经验,要清除msdb中的日志或历史记录,我们需要一个存储过程,比如sp_delete_backuphistorysp_purge_jobhistory
  1. 在数据库msdb中的所有存储过程中搜索,最后找到“sp_maintplan_delete_log”
  2. 我也在微软文档中搜索,在维护计划向导找到的只有下图中标注的这句话。
    图片.png
  3. 为了清理此表dbo.sysmainplan_logdetail,我在带有时间戳的循环中使用此存储过程,不填充T-log文件,只保留最后30天:
DECLARE @date datetime, @sql nvarchar(1000)
 
SET @date = CONVERT(nvarchar(10), getdate()-720 , 21)
 
WHILE @date <= CONVERT(nvarchar(10), getdate()-30 , 21)
 
            BEGIN
 
                        SET @sql = N'EXEC [msdb].[dbo].[sp_maintplan_delete_log]  @oldest_time = {ts''' + CONVERT(nvarchar(23), @date , 21) + N'''}'
 
                        EXEC(@sql)
 
                        SET @date = @date +30
 
                        PRINT @sql
 
            END

图片.png
4. 上图中可以看到,在2020年1月之前有5709个记录…😳
清理后,表dbo.sysmainplan_logdetail的记录数从13205减少到152,且文件大小从2.3GB减小到35MB,如下图:
图片.png
为了完整起见,最好的做法是执行与Microsoft文档中相同的操作,即使用存储过程sp_purge_jobhistory,sp_delete_backuphistory。
我还建议创建一个日常任务来清理维护计划中所有的历史记录。😎

最后修改时间:2022-05-09 10:36:29
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
1人已赞赏
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论