469,917 Members | 1,666 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,917 developers. It's quick & easy.

Tip - speed up "Database Properties" in Enterprise Manager

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
0 1373

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Hale | last post: by
5 posts views Thread by VB Programmer | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.