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?

  • Do you have multiple filegroups? Do you have multiple files? – Stijn Wynants Jul 19 '17 at 11:30
  • I have one filegroups, data file and log file. – Yunus UYANIK Jul 19 '17 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 '17 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 '17 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 '17 at 14:06
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.

  • 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 '17 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 '17 at 11:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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