Better Way of Releasing Massive Unused storage from Data Files to Server

by JayM   Last Updated November 15, 2017 22:06 PM

After we did massive truncate of data, there is a huge unused space in multiple databases data files, and I wonder what would be the best way of releasing it back. I know most of the experts are against it, I am not talking about single digit GB space, its more 56% unused space out of 800GB data files, and there are many more files in the server.

SQL Version - 2014 Standard ( this cannot be changed, and no more storage can be added)

I know the below options could work, but also potentially cause fragmentation.

  1. DBCC SHRINK (causes massive fragmentation and uses back some space while update status).
  2. Moving the Primary File Group table to Secondary File Group.
  3. Adhoc shrink in Chunks (this also causes fragmentation).

Any suggestions above this and new ideas are greatly appreciated.

Answers 1

Paul Randal who led the storage engine team in SQL 2005 recommended moving the data to a different filegroup. Personally I like this approach because it caused the least amount of free space left at the end of the process. I believe this was in his old MCM training videos.

Also having our databases in the PRIMARY filegroup led to a potential disaster when the GAM/SGAM for the primary filegroup was corrupted due to all the writes on a bad IO subsystem. Since the DB boot page is on the primary file group and other reasons, I now always keep the PRIMARY filegroup empty to limit corruption potential to it. Also if you do piecemeal restore and you restore the Primary filegroup, your database is technically online reducing the errors you are spitting out while the rest of the DB is up.

Ali Razeghi
Ali Razeghi
November 15, 2017 21:37 PM

Related Questions

DBCC Refuses to Shrink a Data File - drive was full

Updated March 06, 2018 01:06 AM

unable to shrink tempdb

Updated March 05, 2018 08:06 AM

When Should You Shrink Your Database

Updated May 24, 2015 21:02 PM