February 16, 2012 at 7:35 pm
Hi All,
Recently we moved a 200 GB table to another server. So the current DB we have about 200 GB free space. I am not sure how long does it take to shrink 200 GB in production environment. We cannot afford to have a downtime to the shrink.
I know the facts that shrink is not the best option. After shriking I need to rebuild all my indexes. Since I am using SQLserver enterprise edition 2008 R2. My backup files are compressed and are very small.
The other point I have absolutely no neccessary to have 200 GB free space added to my OS. I have enough free space. 🙂
Here is my question.
1) Can I leave the 200 GB free space in my data files. Any implication in performance.
2) Can I plan to shrink the data files day by day. I knew I can stop the dbcc shrinkfile and the amount of space gained in a day is still retained on the data file.
Can you people suggest the best method.
Thanks.
--- Babu
February 16, 2012 at 9:23 pm
it seems that u don't require the space actually. Then y are you trying to reclaim the space ? additionally if you really wanted to reclaim it you should have shrink the log file as next step after restoration.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 16, 2012 at 9:23 pm
it seems that u don't require the space actually. Then y are you trying to reclaim the space ? additionally if you really wanted to reclaim it you should have shrink the log file as next step after restoration.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 16, 2012 at 9:45 pm
Hi Bhuvanesh,
I am not trying to reclaim the space. But I am just curious what will happen if the empty space is available in the data files. As such this is not an outage or critical. But is there any reason for me to delete/not delete the empty space. That is what my concern is.
--- Babu
February 17, 2012 at 1:13 am
baabhu (2/16/2012)
Hi Bhuvanesh,I am not trying to reclaim the space. But I am just curious what will happen if the empty space is available in the data files. As such this is not an outage or critical. But is there any reason for me to delete/not delete the empty space. That is what my concern is.
--- Babu
Leave the 200GB white space in the data file, it will cause you no problems as you wont need to grow your data file for a while due to the extra space.
Shrinking the file will cause more harm than good as you will start to introduce fragmentation at DB and disk level which can affect performance if not resolved.
If you do want the space back, shrink the file to a size leaving enough room for at least a years growth, defragment your drives, rebuild all your indexes, if you have enterprise edition you can do this online, if you have standard then indexes need to be rebuilt offline which would cause downtime for that table while the index is rebuilding
February 17, 2012 at 3:03 am
Thank you Antony. I decide to leave the file without shrinking.
---Babu
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply