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)