Cleaning up MSDB

Leave a comment (0) Go to comments

Over time as a DBA you will see the size of the MSDB database grow.  If performance becomes an issue in MSDB you may want to purge, but it’s important to understand what’s causing the growth.  One critical area of this growth is backup and restore history.  Below is a list of tables used to maintain backup history as backups and restores occur:

  • backupfile
  • backupfilegroup
  • backupmediafamily
  • backupmediaset
  • backupset
  • restorefile
  • restorefilegroup
  • restorehistory

To purge data from these tables as of a given date, make sure the option to update system tables is ‘on’ and run this SQL statement:

use
msdb

go

exec sp_delete_backuphistory ’2009-04-02′


go

EOF - Cleaning up MSDB, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.