I have been getting this question a lot lately and felt it was a good thing to share.
When you are in SSMS (SQL Server Management Studio) and you try and open the dialogue to run a backup or a restore it takes forever to open. We are talking go take the client out for a long lunch, come back, go to three other meetings that take twice as long as they should and maybe, just maybe the wizard will be up. Once it’s up things may still not work or you may get some random exception error about “being out of RAM” but you check and you have plenty of RAM. So what gives? Do the SQL god’s just hate you that much? Is your server failing, corrupt, going to start on fire? Chances are probably not but there may be something we can do to fix it.
My guess is you have already checked the obvious things. Things like is there enough disk space, do I really have enough RAM like mentioned above, and am I over taxing the CPU and you have concluded that these are all within normal operating parameters.
So, What Gives?
So you are a good DBA or the person who is in charge of the SQL server and you are running your backups. Your full backups, differentials, and all those transactional backups. Did you know that every time one of those backups runs you write a backup record in the msdb database? Doesn’t sound like a big deal, but lets do a little math. Say you are really good at your job and you don’t want there to ever be a chance of losing data, so you are running transaction backups every 30 min and differentials every 12 hours and a full backup every day. Some of you are thinking this guy is crazy who would ever run that many backups? In a highly transactional SQL database I have seen transactional backups as often as every 5 minutes, differentials every hour ect. It all comes down to your level of risk. How much data can you afford to lose if something went wrong? If you need help with making a plan see my other blog post, What Is Your Backup Strategy?
. So back to our original estimate. My math is 48 transaction backups plus two differentials and one full backup for a grand total of 51 records per day. Now 51 records per day multiplied by 365 days that is 18,615 records. Now that is just for one database. If your like me and you have 190 databases on a server you now have 3,536,850. Now multiply that by 4 years. 14,147,400
. Now your saying to yourself, Blair I know you can use a calculator and multiply numbers but what is wrong with my SQL server? Each time you try and run that backup or restore dialogue the server is trying to parse and load all of the backup history for the given database you are working with. That’s a lot of data to go through especially when by default there are no indexes in msdb.
So how do we fix it?!?
There are few ways of doing this but all are doing the same thing. We are cleaning up the backup history.
Method 1: Building and Running a Maintenance Plan
1. Start the New Maintenance Plan Wizard
2. Give your plan a name
3. Select History Cleanup Task from the Toolbox (Note: the Toolbox may be minimized in the upper left corner of the window)
4. Set your schedule and time frame. I like to delete anything older than 6 weeks. If I have to restore to something older than that I will not likely be overwriting as much as making a copy.
5. Now depending on how much data you have this could take a very long time and/or fail completely.
Method 2: Manual Code
If you have a lot of data I suggest you create indexes on the msdb database and then manually run a delete command in smaller batches.
This query will show you the oldest 50 records so you know how far back your data goes:
--Show history (oldest 50 records)
SELECT TOP 50
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.name AS backupset_name,
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
--WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
Run the following to index the backup and restore sets:
-- create indexes on the backupset column
CREATE INDEX IX_backupset_backup_set_iid ON backupset(backup_set_id)
CREATE INDEX IX_backupset_backup_set_uuiid ON backupset(backup_set_uuid)
CREATE INDEX IX_backupset_media_set_iid ON backupset(media_set_id)
CREATE INDEX IX_backupset_backup_finish_date_i ON backupset(backup_finish_date)
CREATE INDEX IX_backupset_backup_start_date_i ON backupset(backup_start_date)
--create index on the restorefilegroup column
CREATE INDEX IX_restorefilegroup_restore_history_iid ON restorefilegroup(restore_history_id)
-- create index on the backupmediaset column
CREATE INDEX IX_backupmediaset_media_set_iid ON backupmediaset(media_set_id)
-- create index on the backupfile column
CREATE INDEX IX_backupfile_backup_set_iid ON backupfile(backup_set_id)
-- create index on the restorefile column
CREATE INDEX IX_restorefile_restore_history_iid ON restorefile(restore_history_id)
-- create index on the backupmediafamily column
CREATE INDEX IX_backupmediafamily_media_set_iid ON backupmediafamily(media_set_id)
-- create indexes on the restorehistory column
CREATE INDEX IX_restorehistory_restore_history_iid ON restorehistory(restore_history_id)
CREATE INDEX IX_restorehistory_backup_set_iid ON restorehistory(backup_set_id)
Finally use enter in your date where you want the delete to stop
EXEC sp_delete_backuphistory @oldest_date = '12/31/2015';
Your backup and restore dialogue boxes should once again flow with ease. Huge shout out to Cory VandenBout
, one of our Sr. Developers and friend, for inspiring and contributing to the above code. Cory’s SQL-Fu is far superior to my own and he greatly helped me in this endeavor.