question

groove15 avatar image
groove15 asked

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 ?
sql-server-2008-r2indexfilegroupspacemdf
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.

1 Answer

· Write an Answer
Tom Staab avatar image
Tom Staab answered
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);
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.