Home Forums SQL Server 2005 Administering MSDB heavy physical I/O and CPU in Activity Monitor RE: MSDB heavy physical I/O and CPU in Activity Monitor

  • I have this script setup as a step within a frequently executed SQL Server Agent maintenance job. It prunes backup history older than 90 days. Just replace the 90 with how ever many days works for you:

    [font="Courier New"]/*

    Purges backup history from the MSDB database that is older than 90 days.

    Use this to identify the oldest backup recorded in the MSDB database: select min(backup_finish_date) from backupset

    */

    DECLARE @sql as nvarchar(max)

    DECLARE @DatePurge as datetime

    SET @DatePurge = CAST(GETDATE() - 90 AS datetime)

    SET @sql = ''

    Set @sql = '

    use msdb

    exec sp_delete_backuphistory ''' + LEFT(CONVERT(VARCHAR, @datepurge, 120), 10) + ''';'

    exec (@sql)[/font]