Reclaim space after filegroup table index move

I have created a secondary filegroup with ndf file on a different partiton and moved a larger index to that new secondary file group. I can see that the space has increased on that new ndf file, but it has not decreased on the original MDF one. I rebuilt the index on the table in question and the size of the original data file increased ?

Any suggestions on how to reclaim space in the original MDF ?

more ▼

asked Nov 10, 2015 at 09:26 PM in Default

avatar image

10 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

When you move data from one file to another (or delete data), SQL Server recognizes that the space has been freed, but it won't automatically shrink the original file. You really don't want it to anyway because it would cause a performance problem later if it had to grow again. You can manually shrink the file if you are certain it is larger than it needs to be (now or in the near future). For example, the following statement will shrink the File1 file in the current database to 100 MB.

 DBCC SHRINKFILE (File1, 100);
more ▼

answered Nov 10, 2015 at 10:15 PM

avatar image

Tom Staab ♦
14.5k 7 15 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 10, 2015 at 09:26 PM

Seen: 50 times

Last Updated: Nov 11, 2015 at 01:51 PM

Copyright 2018 Redgate Software. Privacy Policy