First published on TECHNET on Oct 26, 2010
The MSDB database is a system database that stores important configuration and historical information relating to SQL Server system functionality like:
-
Jobs
-
Log Shipping
-
Alerts and Notifications
-
Operators
-
Policies
-
Database Mail
-
Mainteance Plans
-
Data Collector
-
Backup and Restore operations
Depending on how you perform certain operations orphaned data can exist that will remain in your MSDB database until it is manually removed.
Let's look at the example of dropping a database. There are a few different ways in SQL Server to accomplish the same task, you can:
-
Use a DROP DATABASE T-SQL Statement
-
Use the SQL Server Management Studio GUI
-
Detach and delete the database files manually
The only option that gives you the choice on removing the backup/restore history of the database you are dropping is if you use the SQL Server Management Studio and check off the "Delete backup and restore history information for databases" check box. 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.
Caring for your system databases is just as important as caring for your user databases. Having a bloated MSDB may not cause your SQL Server to fail but you could be storing (and backing up) hundreds of thousands of rows of old data that should be removed from your MSDB database permanently.
As with any script found online it is important to review and test the code to ensure that it is has the intended results in your environment.
_____________________________________________________________________________________________
Colin Stasiuk (MCP, MCTS SQL 2005/2008, MCITP DBDEV, and MCITP DBA) is an accomplished Microsoft SQL Server DBA who has been working with SQL Server since 1996. He is the founder of Benchmark IT Consulting and his specialties include SQL Server Administration, Performance Tuning, Security, Best Practice / Standards, Upgrades, and Consolidation. Colin is a proud PASS member, President of EDMPASS (The Edmonton Chapter of PASS), and has recently co-authored a book on SQL Server 2008 Policy Based Management.
Follow Colin Stasiuk On Twitter
