3

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

  • If (like the question you reference) you are on SQL Server, then file sizes only change automatically if you explicitly configure that. Auto growing files is normally recommended, as the actual growth of your DB and files may be different than you expect. Using the auto shrink option is generally not recommended; in most cases, files that are shrunk will need to grow again, often soon. File shrinks are best done manually, and should only be done if a large amount of empty space exists because of an unusual event (as in your case). – RDFozz Mar 9 '18 at 20:25
  • Hi. I don't have autoshrink on... I only manually do that to log files after a test db from production is made and converting to simple mode, or other testing, like the above - but never autoshrink. – Bill Cummings Mar 14 '18 at 16:46
  • ... and thanks for adding the sql server tag... – Bill Cummings Mar 14 '18 at 16:47
1

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.

  • Hi Randolph - I did do that - there are 3 columns in the data, Shrunk, Reorg, and full/simple. each row represents steps in the test... This is why I was surprised. There was change, just not the expected change. – Bill Cummings Mar 14 '18 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.