So how do you drop databases?
Do you use a simple DROP DATABASE statement?
Do you detach the database and remove the files manually?
Do you use the GUI?
Run these two quick and dirty queries for me:
SELECT COUNT(*)
FROM msdb.dbo.backupset
SELECT COUNT(*)
FROM msdb.dbo.backupset
WHERE database_name NOT IN (SELECT name FROM master.dbo.sysdatabases)
So how much of your MSDB database is filled with backup history of databases that have long since been dropped from your instance?
MSDB love and care is not something that usually gets alot of press but if you’re a stickler for wanting to keep things neat and tidy take a look at the information in your MSDB and whether or not you’re storing (and backing up) more data then you need to.
Want to rid yourself of some MSDB bloating?
SELECT DISTINCT(database_name)
FROM msdb.dbo.backupset
WHERE database_name NOT IN (SELECT name FROM master.dbo.sysdatabases)
So now that you have a list of those old databases that still have backup history kicking around… now what?
Don’t jump the gun and write a DELETE statement for msdb.dbo.backupset… what about the other backup tables like backupfile, etc?
Answer: msdb.dbo.sp_delete_database_backuphistory
EXEC msdb.dbo.sp_delete_database_backuphistory @db_nm = N'<<DATABASE_NAME>>'
Using this system stored procedure you’ll ensure that you’ve removed all the backup history for the specified database.
(As always, please use with caution)
Enjoy!!
