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.
DBCC SHRINK(causes massive fragmentation and uses back some space while update status).
Any suggestions above this and new ideas are greatly appreciated.
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.