Recently I was hired for my On Demand (55 minutes) Service by an organization and they had a very interesting situation. To be very honest, I was initially lost for few moments when they described this situation to me and wanted me to solve their error. They were facing a very unique situation where their SSMS was working just fine but when they try to restore any database, SSMS Restore Database Wizard will take forever to open.
The DBAs there were very much surprised with this issue and they had already spent a lot of time on the server already. After unsuccessful attempts, they decided to reach out to me and wanted me to solve their error. Let us see what are the various efforts which we attempted during the call to solve the problem and which one exactly worked.
Failed Attempt 1: Re-install SSMS
This did not work for us.
Failed Attempt 2: Install the latest version of SSMS
Again, just like reinstallation SSMS, the latest version of SQL Server Management Studio did not work.
Failed Attempt 3: Increased the memory to OS
As we were on a virtual machine, we tried to increase the memory on OS but that was not successful as well.
Failed Attempt 4: Attempt to restore another database
It does not matter which database we tried to restore the SSMS Restore Database Wizard would not come up easily.
Failed Attempt 5: Tried when the server is not busy
Honestly, that did not work out as well.
Successful Attempt: Delete Backup History
Well, after spending over 30 minutes on various different unsuccessful attempts, just like a senior DBA, I was also a bit lost for the moment. However, when noticed that only their restore database wizard is slow but no other part in the SSMS, I suddenly remembered about backup history.
Whenever we open Restore Database Wizard at that time SQL Server retrieves entire history of the database backup from the table msdb.dbo.backupset and displayed under the section Backup sets to restore. I checked with the DBA and they were not deleting any backup history at all as a part of the maintenance process.
The next thing which we did was to delete the backup history with the following command, where I deleted all the history before the date January 1, 2018.
USE msdb; EXEC sp_delete_backuphistory @oldest_date = '01/01/2018';
Once I deleted their history, suddenly their restore database wizard started to open very quickly.
Lesson learned that you must have a proper maintenance plan in place. Deleting the backup history is just one of the step of the maintenance task, you can reach out to me at pinal @ sqlauthority.com and in an hour we can put a very robust maintenance plan which will not give you unexpected surprises as such described in today’s blog post.
Reference: Pinal Dave (https://blog.sqlauthority.com)
17 Comments. Leave new
This is very strange situation an I’ll never delete backup history to try to solve this issue. Backup history can be deleted after business approval
True. if MSDB is huge due the backup history data and we try clean the history , I have seen for some reason TEMPDB grows during the clean up task.
This is fantastic to know. I had the same issue. Thanks Pinal!
If you have a ridiculous amount of history covering many years, the delete can take a long time…MS has no indexes on the backupset group of tables by default. I have had to add indexes and delete data one day at a time in the past for this very issue. –Kevin3NF
This is so very true. When taking over a decade-old server where backup history has never been deleted. Executing the proc to delete the history will blow up tempdb. I had to write a script to batch-delete the history when I find myself in this situation.
I had this same problem on one of my servers on which we run our own backup / checkdb / index maintenance, but have not been addressing backup history. SSMS was getting progressively slower when opening the restore wizard, taking several minutes (< 5 minutes…but still a long time) just to display the screen.
Instead of just running the stored procedure, I decided to look more carefully at it.
I first backed up history (just in case):
USE mydatabase
SELECT * INTO dbo.SQLBackupset_20190526 FROM msdb.dbo.backupset
I then had SSMS script out the stored procedure. This worked fine. I deleted the CREATE PROCEDURE, the top level BEGIN/END, and added a SET @oldest_date = '4/1/2019'. I executed…and it ran just fine. It took just a few seconds, and had no errors.
(I first reviewed what the stored procedure—now a query–did…just for my own knowledge.)
What is strange: there were only a total of 8,277 rows in the table for all databases, and only 1,403 rows for the database I was trying to restore. This includes log files. Is this a lot of backups? Yes, and of course most of these are not actually available…and so preserving the history makes no sense.
Is this so much data that it should cripple SSMS? No…not at all. In terms of rows, this isn't really a lot.
FYI, this was on SSMS v17.9.1 against: Microsoft SQL Server 2016 (SP2-GDR) (KB4293802) – 13.0.5081.1 (X64) Jul 20 2018 22:12:40 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: )
Thank you for your post! It was very helpful. Now I’ll be sure to delete backup history regularly.
i always say you are the best. So i say it one more time. I was searching but couldn’t find the answer ,i was waiting for years to open the select button to choose the backup when trying to restore. by running your commands EXEC sp_delete_backuphistory @oldest_date = ’07/18/2019′; on msdb database ,saved my life. Thank you for your existence…
Hi Pinal Dave, What do you suggest for this exact same problem – but on a brand new install of SQL Server? I create a brand new database and try to restore and the file selection dialog never appears. The database has no backup history and actually has no tables. Any ideas?
Mike
Same situation here. SQL 2017 Standard. In fact, I’m moving my live databases over using the restore wizard, that’s how new it is (select * from msdb..backupset shows 8 results for the whole server!). I can, however, connect to this server from my workstation (using SSMS 2012) and the restore wizard there only takes a few seconds to open the ‘Add Device’ dialog. This is how I restored all of the DB’s but it still confuses me why it takes so long directly on the server’s 2017 SSMS.
Why does the file selection dialog take so long to appear? I tried your solution, but same problem after I start restore wizard, click on “Device” then click on the dot-dot-dot button to choose file. I find if I wait 5-10 minutes, it will appear. I’ve been doing SQL for over 20 years. This one is odd.
Very useful for a development server.
Thank you.
Just posting here to give some metrics, with a database that has transaction log backups every 15 minutes.
We had been retaining 180 days worth of history. I needed to restore a DB and found that it was taking >12 minutes to open the dialog (I actually don’t know how long, — I gave up at the 12 minute mark and killed SSMS). We dropped the history down to 30 days retention (so 8854 rows in msdb..backupset down to 1553), and then tried again. This time it took 3m12s to open the dialog. So I dropped the history down to 4 days (230 rows) and this time it only took 29s.
Fantastic article/post as usual. THANK you Pinal. Now… This, and subsequent comments, raise questions. Some say there was no history at all and still it would take a long time to open.
1- Has anyone come up with the answer as to why?
Someone mentioned indexing the table(s)/view(s)/object(s). I am most interested in this idea. So,…
2- Any insights as to what to index, how, and also very important, any deleterious issues that could be caused by creating such index(es)? Meaning: Is it OK to go ahead and create these indexes, or because these are technically system tables meddling with them would be a no-go, maybe?
Help appreciated. TIA, Raphael