Shrinking file

Question

Hi Team,

We have file group called FG1 (default file grop) and size is 1 TB.

I created additional file groups and moved 80% of data from FG1.

But still Size of the FG1 is showing 1 TB, why like this, as per my knowledge it should reduce?

is it necessary to Shrink?

This is SQL Server 2016 environment.

Answer 1

Hi @Vijay Kumar ,
Agree with others. And you can use the following query to view the physical size, used space size and free space size of filegroup:

DECLARE @FileGroupName sysname = N'filegroupname';  
   
;WITH src AS  
(  
  SELECT FG          = fg.name,   
         FileID      = f.file_id,  
         LogicalName = f.name,  
         [Path]      = f.physical_name,   
         FileSizeMB  = f.size/128.0,   
         UsedSpaceMB = CONVERT(bigint, FILEPROPERTY(f.[name], 'SpaceUsed'))/128.0,   
         GrowthMB    = CASE f.is_percent_growth WHEN 1 THEN NULL ELSE f.growth/128.0 END,  
         MaxSizeMB   = NULLIF(f.max_size, -1)/128.0,  
         DriveSizeMB = vs.total_bytes/1048576.0,  
         DriveFreeMB = vs.available_bytes/1048576.0  
  FROM sys.database_files AS f  
  INNER JOIN sys.filegroups AS fg  
        ON f.data_space_id = fg.data_space_id  
  CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.file_id) AS vs  
  WHERE fg.name = COALESCE(@FileGroupName, fg.name)  
)  
SELECT [Filegroup] = FG, FileID, LogicalName, [Path],  
  FileSizeMB  = CONVERT(decimal(18,2), FileSizeMB),  
  UsedSpaceMB,  
  FreeSpaceMB = CONVERT(decimal(18,2), FileSizeMB-UsedSpaceMB),  
  [%]         = CONVERT(decimal(5,2), 100.0*(FileSizeMB-UsedSpaceMB)/FileSizeMB),  
  GrowthMB    = COALESCE(RTRIM(CONVERT(decimal(18,2), GrowthMB)), '% warning!'),  
  MaxSizeMB,  
  DriveSizeMB,  
  DriveFreeMB,  
  [%]         = CONVERT(decimal(5,2), 100.0*(DriveFreeMB)/DriveSizeMB)  
FROM src  
ORDER BY FG, LogicalName;  

If you have any other question, please feel free to let us know.

Best Regards,
Amelia

Answer 2

But still Size of the FG1 is showing 1 TB, why like this, as per my knowledge it should reduce?

SQL Server never shrinks a file on it's own, because it's an expensive operation and will slow down current operations. You have to shrink it manually. Keep in mind, that shrinking a file will cause index fragmentation.

Answer 3

In addition to other posts: While normally recommend against shrinking, this is certainly one of the situations where it does make sense. You have permanently moved data elsewhere. So shrink the file to some space - but not the bare minimum. When you're done, you need to rebuild your indexes, and they will need some headroom.

And, yes, you have to ask for a shrink explicitly.

Answer 4

This is how I did for one of my requirement.
https://sqlzealots.com/2020/07/18/a-way-to-shrinkfile-datafile-in-sql-server/