SQL Free space management I have 1.8TB AX database.

File Size : 1851427.75 MB

Free Space : 502837.13 MB

If free spaces will be used in the future, I don't want to shrink. Because it's taking a long time. (and index fragmentation)

Daily observations result:


Day Free space 492.435,75 MB (26%)
Day Free space 485.750,25 MB (26%)
Day Free space 502.875,75 MB (27%)


Sometimes the free space is reduced, sometimes data file size grows. 

Why there is growth when there is free space, how SQL decides?
SQL Server tracks free space within data files and uses that when additional space is needed. Data files will grow only when there is not enough existing free space to satisfy allocation needs.  Shrinking is not necessary for this space to be reused and should be generally be avoided for the reasons you mentioned. 

If you observe occasional file growth even though sufficient free space exists, this may be due to short term space needs. For example, rebuilding a clustered index will require about 120% of the table size as free space during the operation that is freed afterward.