I'm having an issue with SSMS SQL Server 2014 (before and after SP1).

When I go and select 'restore database' from the server and select a database, it just sits there and spin and spins. More than 24 hours with no results during one initial test.

Test's I've done:

Tested against the same database using both SSMS 2008 R2 and 2014/SP1 both on the server and on my laptop.

Using SSMS SQL Server 2014:

7 days in MSDB backupfile 1.9 mil records didn't finish in the time I took lunch.

After purging...

3 days in MSDB backupfile 767k records took 1 minute 31 seconds.

Using SSMS SQL Server 2008 R2:

90 days in msdb dbo.backupfile 6.7 mil records was instant and finished loading in less than 3 seconds.

I ran a trace while testing using both versions against both databases...

  • When SSMS 2014 was executing the trace files would grow to almost 500MB...
  • When SSMS 2008R2 was executing the trace file grew to 260k

I've also applied various indexes to the msdb database from other posts online. It helped a little when the records were reduced, but not enough to the desired effect.

So, in conclusion I think it might be 2014 SSMS that is the issue...thoughts?

  • Well I think the underlying issue is that you're retaining way too much backup history. SSMS not handling it is more of a symptom than a problem IMHO. :-) – Aaron Bertrand Jun 25 '15 at 20:56
  • 1
    Ola Hallengrens maintenance scripts include a SQL Agent Job that will keep MSDB clean and help to prevent these issues – James Anderson Jun 26 '15 at 7:23
  • @Aaron, I would agree with you except it works fine in SSMS 2008 R2 without any slowness. – Jeff Jun 27 '15 at 1:42
  • 1
    90 days, 30 days, 7 days, 3 days, still didn't matter, still was slow. The trace produced almost 500MB of transactions to load 3 days worth for 2014. 2008, only 260k of transactions for the same database/server. Something isn't the same, something seems wrong. – Jeff Jun 27 '15 at 2:10
  • 1
    @Gizmo, glad I'm not the only one who has experienced this now. Now, if we can get a fix for this it would be nice. – Jeff Jul 25 '16 at 21:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.