SQLServer msdb备份信息清除

SQLServer msdb备份信息清除

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

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

select OBJECT_NAME(id),rows from msdb.sys.sysindexes where indid in(0,1)and rows > 0order 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，可停止记录备份日志条目。

dbcc traceon(3226,-1)