Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2004-11-12 : 04:53:03
|
| Colleague of mine needed to transfer a database from ServerA to ServerB yesterday.5GB MDF file, 500MB .BAK file.He set up the restore to show 1% (STATS = 1)The restore took 20 minutes before the first 1% appeared, and then 10 minutes to complete the restore.Should it take 30 minutes to restore? (it a reasonably decent server, not too busy)Is the fact that a 5GB MDF results in only a 500MB backup an indication o serious need for defrag or something similar?I seem to be having more than my fair share of problems at the moment!Kristen |
|
|
pyeoh
Starting Member
18 Posts |
Posted - 2004-11-12 : 05:41:27
|
| First 20 minutes was probably spent creating the 5GB file. The excess of 4.5 Gig is probably unused space, which you can remove using DBCC SHRINKFILE or DBCC SHRINKDATABASE commands. Might be good to know how it grew to that size in the first place.Peter Yeohhttp://www.yohz.comNeed smaller SQL2K backups? Use MiniSQLBackup Lite, free! |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-12 : 05:57:40
|
| 20 minutes seems a long time to create a 5GB file, doesn't it?I believe there were loads of housekeeping deletions recently, but the database is used 24/7 so they were reluctant to shrink it in case it upset access during that time. The database is going to grow again.But I don't know if that's a reasonable strategy!Kristen |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-11-12 : 05:59:56
|
| In a 24/7 environment....the cost of BRP (in this case 30mins) might be more a more in your face cost than 1-2-3 minutes a day doing housekeeping.There must be a happy medium. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-12 : 06:03:38
|
| The restore was onto another machine (for Dev work), so no downtime of real database. Or were you meaning if the production database had to be restored? Would that be 20 minutes quicker because the 4GB database file already exists?I ought to get them to look at how badly defragmented the database is I suppose. Perhaps we should BCP everything out, script the database, drop the lot, recreated and BCP in?Kristen |
|
|
|
|
|
|
Slow restore