SQLSERVER--定期清理维护作业的历史记录

刚删除一个数据库时，在清理数据库备份历史记录时，执行超过近10分钟还未完成，随时查了下，吓死宝宝啦，逻辑读操作竟然高达8000万次以上！

通过UI进行删除数据库时，会默认勾选上“删除数据库备份和还原历史记录信息”，作为多年的老司机，删除数据库应该写脚本进行删除，即使使用UI删除，也应该不勾选该选项，但一时偷懒，直接点执行，导致该操作消耗大量逻辑IO和CPU并持续10分钟还不能成功完成。

勾选上“删除数据库备份和还原历史记录信息”后，会执行下面语句：

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'monitor'

GO

执行该存储过程后，会在msdb数据库中嵌套地删除备份相关的N张表，其中一条删除语句如下：

 DELETE msdb.dbo.backupmediafamily

 FROM   msdb.dbo.backupmediafamily bmf

 WHERE  bmf.media_set_id IN ( SELECT    media_set_id

                              FROM      @media_set_id )

        AND ( ( SELECT  COUNT(*)

                FROM    msdb.dbo.backupset

                WHERE   media_set_id = bmf.media_set_id

              ) = 0 )

当备份和还原历史记录信息较多的时候，删除操作消耗的资源会成几何数增长，由于该服务器用作日志传送服务器，承载很多个数据库的日志传送，因此相关备份表中存有大量数据，导致删除操作长时间不能完成。

解决办法：

定期执行下面脚本来清理备份还原数据：

--设置历史记录保存期限为1天

DECLARE @keepMinutes BIGINT

SET @keepMinutes= 60*24

DECLARE @expiredDT NVARCHAR(100)

SELECT  @expiredDT = dbo.ufn_FormatDate(DATEADD(MINUTE, 0 - @keepMinutes,

                                                GETDATE()),

                                        'yyyy-MM-ddTHH:mm:ss')

EXEC msdb.dbo.sp_delete_backuphistory @expiredDT

EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @expiredDT

EXEC msdb.dbo.sp_maintplan_delete_log NULL, NULL, @expiredDT

上面脚本中使用到一个日期格式转换函数，代码为：

/****** Object:  UserDefinedFunction [dbo].[ufn_FormatDate]    Script Date: 2015/11/24 19:40:45 ******/

DROP FUNCTION [dbo].[ufn_FormatDate]

GO

/****** Object:  UserDefinedFunction [dbo].[ufn_FormatDate]    Script Date: 2015/11/24 19:40:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--====================================

--将时间转换成制定格式的字符串

CREATE FUNCTION [dbo].[ufn_FormatDate]

   (

     @Datetime DATETIME ,

     @FormatMask VARCHAR(32)

   )

RETURNS VARCHAR(32)

AS

   BEGIN

       DECLARE @StringDate VARCHAR(32)

       SET @StringDate = @FormatMask

       IF ( CHARINDEX('YYYY', @StringDate) > 0 )

           SET @StringDate = REPLACE(@StringDate, 'YYYY',

                                     DATENAME(YY, @Datetime))

       IF ( CHARINDEX('YY', @StringDate) > 0 )

           SET @StringDate = REPLACE(@StringDate, 'YY',

                                     RIGHT(DATENAME(YY, @Datetime), 2))

       IF ( CHARINDEX('MM', @StringDate) > 0 )

           SET @StringDate = REPLACE(@StringDate, 'MM',

                                     RIGHT('' + CONVERT(VARCHAR, DATEPART(MM,

                                                             @Datetime)), 2))

       IF ( CHARINDEX('DD', @StringDate) > 0 )

           SET @StringDate = REPLACE(@StringDate, 'DD',

                                     RIGHT('' + DATENAME(DD, @Datetime), 2))

       IF ( CHARINDEX('HH', @StringDate) > 0 )

           SET @StringDate = REPLACE(@StringDate, 'HH',

                                     RIGHT('' + DATENAME(HH, @Datetime), 2))

       IF ( CHARINDEX('mm', @StringDate) > 0 )

           SET @StringDate = REPLACE(@StringDate, 'mm',

                                     RIGHT('' + DATENAME(mm, @Datetime), 2))

       IF ( CHARINDEX('ss', @StringDate) > 0 )

           SET @StringDate = REPLACE(@StringDate, 'ss',

                                     RIGHT('' + DATENAME(ss, @Datetime), 2))

       IF ( CHARINDEX('ms', @StringDate) > 0 )

           SET @StringDate = REPLACE(@StringDate, 'ms',

                                     RIGHT('' + DATENAME(ms, @Datetime), 2))

       RETURN @StringDate

   END

--====================================

GO

没多少技术含量，厚脸拿出来供初学者学习下！

============================================