3

When specifying the Backup location by trying to click the Add button the dialog hangs and does not recover. This happens on all of our SQL 2000/2005/2008 environments. Could it be due to them running on virtual servers?

Improve this question
1
  • Does it still hang if you try it from directly on the server?
    – Ian Chamberland
    Mar 15, 2011 at 3:57

2 Answers 2

Reset to default
2

If you have a lot of backups that have been taken then it can take a very long time for the database engine to return the information requested. Because of this it is recommended that you always clean out the old data from the msdb database. Microsoft provides a stored procedure to do this which is called sp_delete_backuphistory. When you have a lot of backup history to process (like you do now) it'll take a long time to run.

You should also tune the MSDB database tables. Geoff posted a blog post with some new indexes you should add.

Improve this answer
1

If you run SQL Profiler during the "Add" operation - what do you see for SQL:BatchCompleted and RPC:Completed? (For example - do you see master.dbo.xp_instance_regread and a query against sys.backup_device).

If you do see that it is getting as far as running queries prior to the hang, the next step I would do is to test the queries via SSMS (outside of the GUI). It may give you more clues as to the root cause.

Improve this answer

You must log in to answer this question.