Sql-server – Why didn’t primary file size change after moving large table to new file group

filegroupssql server

After looking and finding the close answer, I'd like to post a follow-up, but separate, question to this one (split db to fit on ssds)

I have a 60 gig database that is growing quickly, due to one table that holds scanned documents. We are pushing to move old paper to scanned docs, and I have no choice that this is how we handle saving scanned documents.
I was thinking about adding a new file group, and move that table to the new FG by recreating the unique clustered index for that table to the new FG.

I did that on a test box and surprisingly, the OS file size of the original MDF did not significantly change, nor did the new secondary FG size up to the corresponding ratio of that one table's size to all the tables.

I was thinking the OS file sizes should change on the order of the size of the table that was moved. What am I missing?

Thanks,
Bill

Results of testing

Best Answer

Once you move data out of a filegroup on SQL Server, you must run a DBCC SHRINKFILE command to reclaim that physical space.

The official documentation has all you need to know about this, but the general technique is as follows (this example is from the documentation, unedited).

USE AdventureWorks2012;  
GO  
-- Truncate the log by changing the database recovery model to SIMPLE.  
ALTER DATABASE AdventureWorks2012  
SET RECOVERY SIMPLE;  
GO  
-- Shrink the truncated log file to 1 MB.  
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);  
GO  
-- Reset the database recovery model.  
ALTER DATABASE AdventureWorks2012  
SET RECOVERY FULL;  
GO

Note that this example assumes your database is called AdventureWorks2012. You'll have to substitute in the logical name for the file and database.

The recovery model change in the above example must be preceded and followed by appropriate backups. Make sure your backups are well-tested, and stored in a secure off-site location as well. While this particular command shouldn't cause any corruption, backups are critical.

Related Question