Sql-server – Better Way of Releasing Massive Unused storage from Data Files to Server

shrinksql server 2014

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).
Moving the Primary File Group table to Secondary File Group.
Adhoc shrink in Chunks (this also causes fragmentation).

Any suggestions above this and new ideas are greatly appreciated.

Best Answer

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.

Related Solutions

Sql-server – Shrink data MDF file on server running on low disk space

Your sp_spaceused provides 2 sets of the data:
- DB Size + Unallocated space - These numbers include BOTH: Data and log file; - Total statistics of RESERVED space for all objects within the database;

I bet your 36 GB of free space are in the Log file.

For real numbers use following query:

SELECT 
    f.file_id as "File Id",
    f.physical_name as "Physical Name",
    CAST(CAST(ROUND(f.Size/128.,3) as DECIMAL(16,3)) as VARCHAR(16)) AS SizeMB,
    CAST(CAST(ROUND(FILEPROPERTY(f.name, 'SpaceUsed')/128.,3) as DECIMAL(16,3)) as VARCHAR(16)) AS UsedSpaceMB,
    RIGHT('  ' + CAST(CAST((1 - FILEPROPERTY(f.name, 'SpaceUsed') * 1./ f.size) * 100 as DECIMAL(5,2)) as VARCHAR(6)),6) AS FreeSpacePrc
    ,  CASE f.file_id WHEN 2 THEN 'Log File' ELSE IsNull(g.name,'N/A') END as [File Group]
FROM sys.database_files as f with (nolock) 
LEFT JOIN sys.filegroups as g with (nolock) ON f.data_space_id = g.data_space_id

Might happen you still have some opened transactions, which hold you from shrinking individual files. See who hols them, close them. Reboot server if necessary. Shrink individual files while nobody accessing the database. switch it temporarily to Single User if necessary:

ALTER DATABASE <Your database> SET SINGLE_USER

Sql-server – Shrinking mdf files – SQL Server 2012

No, shrinking a file or a database never deletes data, at least not data that hasn't been marked to be deleted via the DELETE FROM or the TRUNCATE TABLE ... T-SQL statements.

The "Reorganize pages before releasing unused space" provides the facility to move data around within the file to allow shrinking the file as much as physically possible. If the minimum size reported by the "shrink file" dialog is 350,000 MB, then you won't be able to shrink the file to anything less than that number.

You may want to look at this question and the associated answers for more details about the pros-and-cons of shrinking databases/files.

Best Answer

Related Solutions

Sql-server – Shrink data MDF file on server running on low disk space

Sql-server – Shrinking mdf files – SQL Server 2012

Related Question