By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,617 Members | 1,792 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,617 IT Pros & Developers. It's quick & easy.

Tip - speed up "Database Properties" in Enterprise Manager

P: n/a
Here's something I found a few months ago, and it seems to come in
handy pretty often, so I figured I'd share.

Disclaimer: you are modifying a SYSTEM TABLE. This may not work in
the next version of SQL Server.

When you right click on a Database in the Enterprise Manager and
choose Properties, in order to show the last backup time, it has to go
through MSDB and find the last backup date for the database. Unless
you're carefully pruning that (via sp_delete_backuphistory), it can
get slow. Eventually, getting database properties can literally take
minutes, which locks up the Enterprise Manager for that period of
time.

After watching via the profiler, I added two indexes in MSDB:

in backupset: 1 index, on "media_set_id" and "backup_finish_date"
in restorefile: 1 index, on "restore_history_id"
(both are nonclustered, though you probably could cluster the
restorefile index)

Now, getting properties takes about 4 seconds.
Hope this helps.
Jul 20 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.