Microsoft – SQLServer – MSDB – Grooming

As a MS SQL Server Instance ages, it starts to show its age in several places.  One of the “hidden” areas is the size of the MSDB database.

The MSDB database is the repository for MS SQL Server Agent activities such as database backups, scheduled job runs, and database plan execution logs.

Begining with MS SQL Server 2005, as part of the Database Maintenance Plans – MS introduces the “History Cleanup Tasks”.  When utilized this task removes historical log entries from the corresponding MSDB tables.

Here are some of the Stored Procedures:

  1. msdb.dbo.sp_delete_backuphistory
  2. msdb.dbo.sp_purge_jobhistory
  3. msdb.dbo.sp_maintplan_delete_log

Quick run down of the Stored Procedures:


sp_delete_backuphistory (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms188328.aspx

Reduces the size of the backup and restore history tables by deleting the entries for backup sets older than the specified date.  

Additional rows are added to the
backup and restore history tables after each backup or restore operation is performed; therefore, we recommend that you periodically
execute sp_delete_backuphistory.

Syntax:
   sp_delete_backuphistory [ @oldest_date = ] 'oldest_date'

Sample:

   declare @dateToday datetime
   declare @datetoKeep datetime

   set @dateToday = getdate()
   set  @iNumberofdays = 60 * -1

   --go back N Number of days
   set @datetoKeep = dateadd(day, @iNumberofdays, @dateToday)

   --remove backup history records earlier than N days
   exec  sp_delete_backuphistory @oldest_date = @datetoKeep


msdb.dbo.sp_purge_jobhistory (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms175044.aspx
Removes the historical log records for a job.

a] Note that the oldest_date option is only available starting
with MS SQL Server 2005


Syntax:

      exec msdb.dbo.sp_purge_jobhistory
      {
           [ @job_name = ] 'job_name' |
         | [ @job_id = ] job_id }
         [ , [ @oldest_date = ] oldest_date ]
      }

Sample:


      declare @dateToday datetime
      declare @datetoKeep datetime

      set @dateToday = getdate()
      set  @iNumberofdays = 60 * -1

      set @datetoKeep = dateadd(day, @iNumberofdays, @dateToday)

      exec msdb.dbo.sp_purge_jobhistory @oldest_date=  @datetoKeep


msdb.dbo.sp_maintplan_delete_log

Removes the maintenance plan execution log.

a] Note that this Stored Procedure “birth” with MS SQL Server 2005

Syntax:


       exec msdb.dbo.sp_maintplan_delete_log
          @plan_id = [plan-id]
        , @subplan_id = [sub-plan-id
        , @oldest_time =  [date];

Sample:


        declare @dateToday datetime
        declare @datetoKeep datetime

        set @dateToday = getdate()
        set @iNumberofdays = 60 * -1

        set @datetoKeep = dateadd(day, @iNumberofdays, @dateToday)

        exec msdb.dbo.sp_maintplan_delete_log
                @plan_id = null
              , @subplan_id = null
              , @oldest_time =  @datetoKeep

For those that have not done a good job of keeping their audit trail grommed, the DBA  might consider utilizing Geoff Hiten indexes.

It is available via:

http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx

 

 

References:

  1. SqlCleanupLargeMSDB
    How to clean up a large MSDB system database
    http://www.aspdeveloper.net/tiki-index.php?page=SqlCleanupLargeMSDB

  2. MSDB Performance Tuning – Author: Geoff N. Hiten – Creating indexes on MSDB
    http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s