April 11, 2014 at 7:51 am
I have a database which is 306 GB and only 171GB of it is used. I need to shrink this database to be able to move it to the correct location. It is currently on a temporary storage. I started shrink data file one week ago and it is still running.
I am not sure if the figures are right in sys.dm_exec_requests? it was suposed to have two more days left but I think it is only going by one percent a day.
Even if I recover 3 GB, it will still be ok to move back to the correct drive.
Could you please let me know how the shrink is perfomed? Is the space released from end of the file? Do you think if I can cancel the shrink and run a shrink again to release the space from end of the file? Thank you. 🙂
Where there is a will... There is a way!
April 11, 2014 at 8:07 am
the file will not be reduced in size until the very end when the shrink process has completed compacting the data. If you tried to shrink it by the full 130GB in one go that would very likely take a long time.
Cancel that shrink (work done so far will not be lost). the size of the file will not reduce as it has been canceled, but then kick off another shrink to reduce the file size in small chunks, so specify a target size i.e.
dbcc shrinkfile(datafile_logical_name,300GB)
repeat as necessary until you are down to the size you require. then you will need to rebuild your indexes as I guess you are aware.
---------------------------------------------------------------------
April 11, 2014 at 8:13 am
This operation is single threaded in sql 2008 and earlier and can only take advantage of 1 CPU (this is why it takes so long) I believe it is multi-threaded in version 2012+
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 11, 2014 at 10:06 am
Hi everyone, Thank you for your replies. I stopped the shrink file and started a new shrink but only reduced the size by 2 GB chunks and it finished in a few seconds. I guess all the work was done during the week and it just had not finished, so now I have 20 GB back and happy! Thank you. :hehe::-)
Where there is a will... There is a way!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply