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 ?
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);