本文探讨了MSDB SQL数据库的维护和清理过程，这对每个DBA来说都是必不可少的。
简介
SQL Server系统数据库--MSDB存储SQL Server代理作业、历史记录、时间表、数据库邮件、服务代理、备份和恢复历史。在许多数据库中，我们运行常规工作、备份、维护计划，由于这些事情，如果你不执行定期清理，MSDB的大小可能会很麻烦。默认情况下，SQL Server不会从历史表中清除数据。
有时，你在试图查看作业历史时，会得到一个错误信息。
执行超时过期。在操作完成之前已经过了超时期，或者服务器没有响应。
这个错误可能是由于MSDB数据库中巨大的表造成的。因此，需要对MSDB数据库进行定期维护。
MSDB SQL数据库属性
- 默认情况下，MSDB数据库有一个主（MSDBData.MDF）和日志（MSDBLog.ldf）交易日志文件。
- 数据和日志文件的自动增长被设置为10%。
- MSDB数据库是在简单恢复模式下。然而，你可以把它转换为完全恢复模式（推荐），并配置交易日志备份。这样，你可以在服务器崩溃后恢复MSDB数据库，并获得最新的备份和恢复历史。
如前所述，MSDB存储了与数据库备份、SQL代理工作、时间表、数据库邮件等相关的信息。因此，让我们通过这个过程来定期清理它们。
清理备份和恢复历史
MSDB数据库在以下表格中存储了数据库备份和恢复历史。
我们可以连接多个表来检索备份信息。例如，下面的T-SQL给出了实例中所有数据库的备份开始日期、结束日期、备份类型和大小。
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
When 'I' THEN 'Differential database'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE()-1 )
ORDER BY
msdb.dbo.backupset.backup_finish_date desc
为了清理数据库备份和恢复表的历史，我们使用sp_delete_backuphistory存储过程。这个存储过程需要保留在备份和恢复表中的最古老的日期。
sp_delete_backuphistory [ @oldest_date = ] 'oldest_date'
下面的脚本从备份和恢复历史表中删除了超过2021年1月1日的记录。
USE msdb;
GO
EXEC sp_delete_backuphistory @oldest_date = 01/01/2020;
你可以使用数据库维护计划--清理历史任务，并配置它来自动清理备份表。
选择维护计划中的清理历史任务 ，然后点击下一步。
在下一页，选择**"删除大于 "历史数据**的参数值。
SQL Server代理工作历史
SQL Server在MSDB SQL数据库表中存储了所有作业的执行情况。除了应用程序或数据库特定的作业外，我们还有完整备份、交易日志备份、索引维护、统计数据更新、索引一致性检查等作业。因此，随着时间的推移，这些表变得巨大。然而，在典型的情况下，我们不需要太多的旧数据来审查作业的执行。
因此，你可以使用sp_purge_jobhistory存储过程来清理作业历史。它的语法如下。
你可以指定工作名称或工作ID，以及历史清理日期。
sp_purge_jobhistory
{ [ @job_name = ] 'job_name' |
| [ @job_id = ] job_id }
[ , [ @oldest_date = ] oldest_date ]
下面的脚本会删除指定工作名称的所有历史记录。
USE msdb ;
GO
EXEC dbo.sp_purge_jobhistory
@job_name = N'DBLogBackups' ;
GO
同样地，下面的脚本会删除指定工作的7天以上的历史。
DECLARE @Date datetime
SET @Date = DATEADD(dd,-7,GETDATE())
EXECUTE dbo.sp_purge_jobhistory @oldest_date = @Date,
@job_name = N'DBLogBackups' ;
如果你没有指定任何工作名称或ID，它将从SQL实例中删除所有工作的历史，按指定的清理日期。
USE msdb ;
GO
EXEC dbo.sp_purge_jobhistory
go
与数据库备份和恢复历史清理类似，你可以为SQL Server Agent工作历史清理配置一个维护计划，如下图所示。
你也可以配置SQL Server代理属性，并在实例级别配置历史清除。右键点击SQL Server Agent，进入历史。在这里，在删除代理历史上打勾，并指定清理日期。
维护计划历史
SQL Server也维护维护计划任务和执行的日志。它使用以下表格来存储维护计划的日志。
- msdb.dbo.sysmaintplan_log
- msdb.dbo.sysmaintplan_logdetails
要清理这些表，请使用存储过程sp_maintplan_delete_log并指定维护计划ID、子计划ID和清理日期。
EXEC sp_maintplan_delete_log @plan_id, @subplan_id, @oldest_time
另外，在历史清理任务中配置 维护计划历史任务，如下所示。
SQL数据库邮件历史清理
如果你使用数据库邮件进行代理工作通知或向用户发送结果，MSDB可能是巨大的。MSDB数据库使用表sysmail_mailitems, sysmail_log, sysmail_attachments来存储数据库邮件。因此，你应该在MSDB的维护任务中也包括数据库邮件历史的清理。
为了清理MSDB数据库的邮件，我们可以使用以下存储过程。
- sysmail_delete_mailitems_sp：它从数据库邮件内部表中永久地删除了电子邮件。这个SP的语法如下。
sysmail_delete_mailitems_sp [ [ @sent_before = ] 'sent_before' ]
[ , [ @sent_status = ] 'sent_status' ]
在@sent_status列中，你可以指定已发送、未发送、重试和失败的值
- sysmail_delete_log_sp：它删除了所有的数据库邮件日志。在@event_type中，你可以指定成功、警告、错误或信息的值。
sysmail_delete_log_sp [ [ @logged_before = ] 'logged_before' ]
[, [ @event_type = ] 'event_type' ]
下面的脚本使用上面指定的存储过程从内部表中清除超过7天的数据库邮件日志。
DECLARE @Date datetime
SET @Date = DATEADD(dd,-7,GETDATE())
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @Date ;
EXECUTE msdb.dbo.sysmail_delete_log_sp @logged_before = @Date ;
GO
清理Suspect_pages表
当SQL Server在访问数据页时遇到以下错误时，会将页面标记为可疑。
- 由于硬件（磁盘）错误引起的循环冗余检查（CRC）导致的错误823
- 错误824，如逻辑错误或撕裂的页面检测
MSDB的SQL数据库中，每一个页面都有一条记录，在 suspect_pages表中出现了823和824的错误。根据微软的文档，DBA负责管理该表。因此，你可以通过删除旧行来清理它。你应该定期使用下面的删除语句来删除行。它可以删除已恢复的、已修复的或已分配的页面。
DELETE FROM msdb..suspect_pages
WHERE (event_type = 4 OR event_type = 5 OR event_type = 7);
GO
event_type列定义了错误的类型。
- 1：823错误
- 2：坏的校验和
- 3：检测到撕裂的页面
- 4：在页面被标记为坏的情况下恢复的
- 5：使用DBCC修复页面
- 7：删除了
清理基于策略管理的执行历史
SQL Server 2008以后的基于策略的管理功能是可用的。它针对数据库或实例评估策略。例如，为了强制xp_cmdshell在一个实例上被禁用，我们可以使用它。SQL Server将策略执行的结果存储在MSDB SQL数据库中。这个表在MSDB中可能是巨大的。
因此，SQL Server在设置过程中，在每个实例中创建了一个作业--syspolicy_purge_history。该作业会清除比策略管理的HistoryRetentionInDays属性中定义的天数更久的数据。
另外，你可以运行下面提到的存储过程来清除执行历史。
EXEC msdb.dbo.sp_syspolicy_create_purge_job
日志运输历史
我们可以在SQL Server中配置日志运输，它在备份和恢复方法上工作。它可以通过应用从主数据库到辅助数据库的连续交易日志备份来准备一个SQL数据库的备用或DR副本。
日志运输将信息存储在MSDB数据库中的表 -log_shipping_monitor_history_detail和log_shipping_monitor_error_detail。
SQL Server使用日志运输的备份和恢复作业自动清理这些表。它使用七天的数据保留。你应该监控日志运输作业和历史表的大小，以避免MSDB的大幅增长。另外，你可以运行存储过程sp_cleanup_log_shipping_history并清理上面列出的表。
总结
MSDB SQL数据库是SQL Server中各种功能的重要和有价值的数据库。SQL Server在系统数据库MSDB中存储了许多日志、历史数据。因此，你应该定期进行数据库维护，清理旧的历史数据。
