Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 7 down vote favorite
1

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.

share|improve this question
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
@HeavenCore: you can see in Profiler what SSMS is doing when you right click on the Databases node. Though I don't see anything in my Profiler for a simple right click, I suppose it crashes when it populates the databases tree. You might want to check MSDB with predefined reports (right click on MSDB -> Reports -> Standard Reports -> Disk usage by top table) or by scripts for other big tables. Maybe it's backup history (clearly in this case), jobs history, mail related issues. You want to verify all those possibilities. – Marian Nov 13 '12 at 11:24

1 Answer

up vote 7 down vote accepted

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.

share|improve this answer
Perfect - thank you – HeavenCore Nov 13 '12 at 11:51
1  
You're welcome. And as @Marian mentions above, you'll probably want to check the other logging tables in msdb as it's likely they also contain 3 years worth of data ( sp_maintplan_delete_log , sp_purge_jobhistory and the sysmail_delete_* SPs detailed here - msdn.microsoft.com/en-us/library/ms177580(v=sql.105).aspx are probably worth investigating) – Stuart Moore Nov 13 '12 at 11:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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