SQL SERVER – Restore Database Wizard in SSMS is Very Slow to Open

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.

SQL SERVER - Restore Database Wizard in SSMS is Very Slow to Open restorewizard1

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.

SQL SERVER - Restore Database Wizard in SSMS is Very Slow to Open restorewizard

Failed Attempt 1: Re-install SSMS

This did not work for us.

Solarwinds

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.

SQL SERVER - Restore Database Wizard in SSMS is Very Slow to Open restorewizard2

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)

Solarwinds
, , , ,
Previous Post
SQL SERVER – Split Comma Separated Value String in a Column Using STRING_SPLIT
Next Post
SQL SERVER – How to Find Free Log Space in SQL Server?

Related Posts

14 Comments. Leave new

  • Vasil Petrov
    May 7, 2018 6:04 pm

    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

    Reply
  • 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.

    Reply
  • Haripriya Naidu
    May 8, 2018 1:55 am

    This is fantastic to know. I had the same issue. Thanks Pinal!

    Reply
  • 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

    Reply
    • Edward Mlynar
      July 31, 2018 8:38 pm

      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.

      Reply
  • 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.

    Reply
  • Dimitris Stathoulis
    July 18, 2019 7:01 pm

    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…

    Reply
  • 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

    Reply
    • 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.

      Reply
  • 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.

    Reply

Leave a Reply

Menu