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

原文地址：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)
  

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

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

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

• 根据我的经验，要清除msdb中的日志或历史记录，我们需要一个存储过程，比如sp_delete_backuphistory或sp_purge_jobhistory。

1. 在数据库msdb中的所有存储过程中搜索，最后找到“sp_maintplan_delete_log”。
2. 我也在微软文档中搜索，在维护计划向导找到的只有下图中标注的这句话。
   
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

4. 上图中可以看到，在2020年1月之前有5709个记录…😳
清理后，表dbo.sysmainplan_logdetail的记录数从13205减少到152，且文件大小从2.3GB减小到35MB，如下图:

为了完整起见，最好的做法是执行与Microsoft文档中相同的操作，即使用存储过程sp_purge_jobhistory，sp_delete_backuphistory。
我还建议创建一个日常任务来清理维护计划中所有的历史记录。😎