May 4, 2010 at 8:26 am
Hello everyone. I was hoping for a little clarification here.
I have a database to move to a new server next week. Due to some bad programming, the database grew to 130GB, when there is only 50GB worth of data.
Now I realize that we don't generally want to shrink a database due to fragmentation, etc. However, since I am moving the database to a new server, I would like to shrink it.
Questions
1) To remove any fragmentation from the system, I would simply need to rebuild all of the indexes once the move is complete correct?
2) Since I have a limited time to complete the move, I am considering shrinking the database in small sections. Does anyone have an experence doing this and if so, the pro/cons of doing this? I have testing the shrink, and it took roughly 6 hours on my staging server to complete. Granted, Staging is not production, but I really cannot afford the downtime on the server.
Thoughts/directions?
Thanks,
Fraggle
May 4, 2010 at 8:34 am
The thing to compare is how long the shrink takes v the copy. If the copy is quicker, and it could be since that isn't a lot of downtime, why shrink?
1. You are correct, but keep in mind that you aren't talking a lot of data, you might want the rebuild anyway.
2. Is this one file? Typically shrinking by file is quicker for some reason.
Keep in mind that you'll be growing this database again to accommodate data. So don't shrink too far.
May 4, 2010 at 10:49 am
Well since I am one of those part-time DBA's and I am pretty sure the SSIS package for the almost 400 tables we have would take a while to build, the shrink is going to take a bit less time....unless you know of something that will do it for me, which would be GREAT!
Yup, Planning to leave about 15% free space above the current size for space as that should last about 6-8 months with the current growth statistics.
Fraggle
May 4, 2010 at 11:22 am
Why use SSIS? Why not backup and restore?
May 4, 2010 at 12:39 pm
Please correct me if I am wrong, but wouldn't the restore process keep the MDF file the exact same size. As such, I am still left with a 130GB MDF file with only 50GB of space being used.
Fraggle
May 4, 2010 at 1:42 pm
Yep, but the backup file won't be 130GB. It will be the data size (with some log records). You can copy 50GB there, restore, then shrink if you really need it. However when you shrink, you'll want to leave space for data growth + reindexing. Not sure if that is 60GB, 70GB or 90GB, but you'd have to make that call.
May 4, 2010 at 1:43 pm
do the normal shrink file process, then goto DB properties and change the mdf (data file) size to 60GB from 130GB.
This will take care of releasing the extra space that has been occuppied by the database.
Let me know if this worked.
Maninder
www.dbanation.com
May 4, 2010 at 1:50 pm
Steve Jones - Editor (5/4/2010)
Yep, but the backup file won't be 130GB. It will be the data size (with some log records). You can copy 50GB there, restore, then shrink if you really need it. However when you shrink, you'll want to leave space for data growth + reindexing. Not sure if that is 60GB, 70GB or 90GB, but you'd have to make that call.
Steve, this is the point. Due to the bad code, I am wanting to shrink. Space ins't an issue on the production server, but it is on the staging server. An this has roughly 80GB of wasted space in it.
Fraggle
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply