Clean up Scheduler History in MSDB

Depending on how you perform certain operations orphaned data can exist that will remain in your MSDB database until it is manually removed.

By completing some tasks in SSMS, you automatically get the option to “Delete backup and restore history information for databases” via a checkbox. For example, when dropping a database via SSMS, this option is checked by default.

When this checkbox is checked before the database is dropped SQL Server will run a system stored procedure called:

sp_delete_database_backuphistory

The sp_delete_database_backuphistory stored procedure will delete information about the specified database from the backup and restore history tables. 

Now that you have this information how can you go back and check to see if you have any orphaned backup/restore history rows?

SELECT database_name, COUNT(backup_set_id) as ‘OrphanedRows’
FROM   msdb.dbo.backupset
WHERE  database_name NOT IN (SELECT name FROM master.dbo.sysdatabases)
GROUP BY database_name

The result set of this query will identify any databases that have backup/restore history that no longer exist on your SQL Server instance.  If this query returns any data you should investigate the databases listed and where appropriate utilize the sp_delete_database_backuphistory system stored procedure to clean up your MSDB database.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

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

%d bloggers like this: