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.
1 Answer 1
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.
Explore related questions
See similar questions with these tags.
select COUNT(1), MIN(backup_start_date) from msdb.dbo.backupseton the problem server? (this will give us the 'amount' of backuphistory you've got atm, and how far back it goes).