question

ymoses avatar image
ymoses asked

DBCC SHRINKFILE

Dear friends, I have a MDF file (FileID 1) with about 200 GB free space(!).
Its size is 1555135 MB (1.5 TB).
I run (for test): DBCC SHRINKFILE (1, 1555130) (trying shrink only 5 MB).
It never finish.
Please help me. Thank you so much. Yossi Moses

sql serveradministrationshrink
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

Ok now why are you partitioning? Performance? Ease of switching in/out data?

If performance then partitioning generally is to much of a overhead then traditional indexes as partition elimination is needed to make it worth while.

You would be better to just add a new drive create new files on that then delete the old drive.


Moving 1.3TB of data isn’t going to be a simple process so your just going to have to let it do what it needs to do.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

Why are you shrinking? What is the end goal? What is your rate of data growth? How long till you use that 200GB again?

Just because you have 200GB free doesn’t mean you should shrink the database, think about growth, think about future maintenance.

Shrinking requires index rebuilds which is going to use space which is going to be 1.5x to 2x of the largest index, so if the largest index is 100GB then you have used the 200GB your shrinking by.


10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ymoses avatar image
ymoses answered

Thank you so much Anthony. I have very big tables in the .MDF file and I want to create file for each table and make the tables partitioned. The .MDF file will not have to big at all after this. and it takes now the space that I need for the new files. I must work table by table, file by file, because I do not have enough space. In the end .MDF file should be shrank much more than 200 GB.

Thank you

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ymoses avatar image
ymoses answered

I need the partitioning for for truncate old months. I have to keep 1 year in table. So every new month I want to get rid from the month 13 months ago.

Also, I think that to rebuild or reorganize indexes in partitioned tables can be made per partition, which is easier than to do it on very big table, am I right?

I thank you very much.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.