4

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:

  1. Day Free space 492.435,75 MB (26%)
  2. Day Free space 485.750,25 MB (26%)
  3. 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?

Improve this question
8
  • Do you have multiple filegroups? Do you have multiple files?
    – Stijn Wynants
    Jul 19, 2017 at 11:30
  • I have one filegroups, data file and log file.
    – Yunus UYANIK
    Jul 19, 2017 at 12:11
  • Do you have a heap table that you truncate and load? Or delete from and load? It's possible that pages are not being deallocated from it.
    – Nic
    Jul 19, 2017 at 13:15
  • In case it isn't clear, file growth consist of adding substantial extra space at once to the data file (making the file larger) when all the space is used. So if the file has 9% free space then it may have just been full and then grown by 10%. With a text comparing tool (Windows "fc" may do), a crude way to identify tables which are growing is to run "DBCC CHECKDB(N'name')" on different dates, save the output, and compare it. There might be data getting added that you've overlooked.
    – Robert Carnegie
    Jul 19, 2017 at 13:27
  • I know the reason for the free space (deleting data). Why data file growing when I have free space? How can I prevent this?
    – Yunus UYANIK
    Jul 19, 2017 at 14:06

1 Answer 1

Reset to default
5

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.

Improve this answer
2
  • I have 700 GB table, but I don't rebuild. What reason else is grow data file? Is there anything I can do to keep the file from growing?
    – Yunus UYANIK
    Jul 19, 2017 at 12:26
  • 1
    @Lyns, you could create an Extended Events trace to capture the database_file_size_changed event along with contextual information to identify the culprit.
    – Dan Guzman
    Aug 1, 2017 at 11:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.