7

We have over a dozen SQL servers, one of which hosts a couple of hundred sage databases, when i right-click on the Databases node in SSMS there is a long delay then i get the following error (and the restore dialogue box doesn’t load):

enter image description here

I presume SSMS is loading backup history from msdb or something in the background and running out of space - does this imply i have excessive backup history in my system databases? If so, what is the best way to maintain it?

I have maintenance plans cleaning up the trn and .bak files on disk but i dont think i maintain any of the logs in the system databases.

HeavenCore
  • 786
  • 10
  • 26

1 Answers1

7

From your reply to my question, then it does appear to be too much backup history. For a one off cleardown, something along these lines will do it:


USE msdb
GO
DECLARE @days DATETIME
SET @days = DATEADD(day,-30,current_timestamp)
EXEC sp_delete_backuphistory @days
GO

That example gets rid of everything over 30 days old, so you may need to change the -30 for a value appropriate for your needs. But remember that you'll then have to build up any restores for backups over 30 days old from hand (may or may not be a problem for you, but thought I'd better mention it).

Going forward you can schedule that to run via a scheduled job to keep you small.

Stuart Moore
  • 2,281
  • 15
  • 17