July 15, 2009 at 3:14 am
one of our customer is trying to restore a database by clicking on right clicking database->all task-> restore database. The restore window is taking ages to open up.
I guessed the problem may be coz of index fragmentation on tables in msdb databse. DBCC SHOWCONTIG of backupmediaset and backupmediafamily shows 1% logical file fragmentation and around 92% extent file fragmentation. Could this be the reason for slowness?
Also what is the difference between these two types of fragmentation?
July 15, 2009 at 3:53 am
It could also be due to the amount of backup history being stored
You can use the stored procedure sp_delete_backuphistory in the MSDB database to trim it down.
July 15, 2009 at 9:30 am
yeah, i did delete data keeping only 3 months.. seems it's improved but still not accetable.. there are around 10 user databases and tran log backups happens every 10 mins.. backupset/backupfile and maintainece tables have still huge no of records.... queying those tables is perfect.. May be coz of the front-end window and large no of records, its taking long time to open up.
July 15, 2009 at 9:56 am
Do you really need 3 months of data?
Take a "common" backup strategy...
Full backups once a week, Differentials daily, Transaction log backups throughout the day... all kept for 2 weeks (ignoring off-site backups, tape archiving etc).
What's the point of keeping the history much beyond 2 weeks?
Are you really likely to restore a database over 2 weeks old?
You can still restore manually without the history in these tables.
July 15, 2009 at 10:03 am
Ian Scarlett (7/15/2009)
What's the point of keeping the history much beyond 2 weeks?
...
...
You can still restore manually without the history in these tables.
I think i should delete more of it. In fact i'll need to create a job that deletes rows in these tables automatically after 2 weeks or so instead of manually firing it.
The backup strategy is good with some of the monthly full backups with retention period of 3 months and others having retention of a week or so.. Anyways, keeping histories of these events is hardly of any use!!.
Thanks Ian.
July 15, 2009 at 10:22 am
Honestly I'd be surprised that you'd actually go back more than a few days from disk. Beyond that you'd likely be looking at tape. Also, if you are dependent on these disks for that long, it's a poor strategy. The chances of a disk failing are high enough that you ought to have separate copies.
The restore dialog shouldn't take that long to read those tables. I think it can be slow if there is slowness on the server or an issue reading one or more of the drives. Especially if you are backing up directly to tape.
July 15, 2009 at 10:30 am
Full backups are sent directly to the tape and the tran log backups are taken on disk and then copied to tape with retention period of a week i guess.
otherwise we never heard of any issues on this server, memory and cpu utlilization is very minimal with around 4 gb of free RAM.
The only reason i can think of is, the front-end window displays all backup sets in one combo list and all backups in the selected backup set in another combo list. may be this is taking time. querying is perfect tho!!
July 15, 2009 at 10:46 am
It might be trying to read the tape headers as well.
I would suggest you not send to tape directly. Send to disk, use NTBackup to throw to tape.
July 15, 2009 at 11:35 am
Steve Jones - Editor (7/15/2009)
It might be trying to read the tape headers as well.
It doesn't... it's just agonisingly slow.
I looked into this some years ago on a system where the transaction log backups were done every 2 minutes directly to disk. On a very well performing machine, it just took an age to populate the GUI, as it populates the list for EVERY database. Even running the stored procedure to delete the history is also ridiculously slow when there is a lot of history to delete.
It's mainly down to a distinct lack of indexes. As long as you keep the minimum amount of history, it's "acceptable".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply