question

VijayKumar768 avatar image
0 Votes"
VijayKumar768 asked AmeliaGu-msft commented

Shrinking file

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.

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @VijayKumar768,
Did the answers help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer.
Best Regards,
Amelia

0 Votes 0 ·
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi @VijayKumar768,
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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered TomPhillips-1744 commented

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.



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi My doubt is,

Already moved data from FG1 to other FG's, so, why still FG1 showing same size ?

0 Votes 0 ·

Do not confuse the physical size of the file with the "used" space inside the file.

You moved the data, which marked the old data as "unused" inside the file. But as Olaf said, the physical size of the file never gets smaller unless you run a shrink file on it. This is normal and expected behavior.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SQLZealots avatar image
0 Votes"
SQLZealots answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.