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.

  • 2
    Possibly, what's the output if you run select COUNT(1), MIN(backup_start_date) from msdb.dbo.backupset on the problem server? (this will give us the 'amount' of backuphistory you've got atm, and how far back it goes). – Stuart Moore Nov 13 '12 at 11:03
  • It's not good! 4,652,902 rows since 2009-10-14 11:46:56.000 – HeavenCore Nov 13 '12 at 11:07
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.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.