SQLServer msdb备份信息清除

2023-02-20 00:00:00 数据数据库专区备份记录

相信数据库运维同学都知道，随着时间的推移，每天备份产生的记录累计越来越多。不仅在系统数据库 msdb 记录下来，SQL Server 的错误日志也会记录相应信息。如果我们不定时清理，长此以往使数据越来越多。

我们查看msdb数据库，看看哪张表数据较多。

select OBJECT_NAME(id),rows 


from msdb.sys.sysindexes 


where indid in(,1)


and rows > 


order by rows desc

有2个系统存储过程，可将这些记录清除。

sp_delete_backuphistory [ @oldest_date = ] 'oldest_date' 


sp_delete_database_backuphistory [ @database_name = ] 'database_name'

sp_delete_backuphistory 按日期删除；

sp_delete_database_backuphistory 按数据库删除；

存储过程将清除以下表数据：

backupfile

backupfilegroup

restorefile

restorefilegroup

restorehistory

backupset

backupmediafamily

backupmediaset

不过，上面并非优方法。当这些表数据量较多的时候，删除是非常慢的，因为这些表之间有外键关联。较好的方法是使用 truncate 清空表数据。毕竟这些记录我们暂时没什么用。在 truncate 前，先把外键给删除掉。

SELECT oMain.name  AS  [主表名称],oSub.name  AS  [子表名称],fk.name AS  [外键名称],MainCol.name AS [主表列名],SubCol.name AS [子表列名]


,'ALTER TABLE [dbo].['+oSub.name+'] DROP CONSTRAINT ['+fk.name+']'


,'ALTER TABLE [dbo].['+oSub.name+']  WITH CHECK ADD FOREIGN KEY(['+SubCol.name +'])REFERENCES [dbo].['+oMain.name+'] (['+MainCol.name+'])'


FROM sys.foreign_keys fk 


JOIN sys.all_objects oSub  ON (fk.parent_object_id = oSub.object_id)


JOIN sys.all_objects oMain ON (fk.referenced_object_id = oMain.object_id)


JOIN sys.foreign_key_columns fkCols ON (fk.object_id = fkCols.constraint_object_id)


JOIN sys.columns SubCol ON (oSub.object_id = SubCol.object_id AND fkCols.parent_column_id = SubCol.column_id)


JOIN sys.columns MainCol ON (oMain.object_id = MainCol.object_id AND fkCols.referenced_column_id = MainCol.column_id)


WHERE oMain.name in('backupfile','backupfilegroup','backupmediafamily','backupset','backupmediaset','restorefile','restorefilegroup','restorehistory')

外键可以不添加了，创建一个定时作业，清空以上表即可。如必要可将外键添加回去。

msdb 相关备份表记录过大的已经解决了，现在我们也把SQLServer错误日志的记录也禁用。启用跟踪标志 3226，可停止记录备份日志条目。

相关文章