0

We have a production SQL Server 2014 running on FILESTREAM with Always On Availability Group.

It has been about 4 years since the servers go live. Recently, we encounter a hard disk space issue on the database server. The database is just taking up too much space. The .mdf and .ldf are taking about 30GB (which is fine for us), and the FileTables should have just taken about 100GB. However, the Database Properties window is showing the database size is about 550GB.

A detailed search on the web has gotten me to this article: FILESTREAM garbage collection with AlwaysOn Availability Groups. I further check the database servers and I think that the extra 400GB of space is taken by the FILESTREAM files that were supposed to be garbage collected.

From the article, if I understand correctly, garbage collection will never kick in when the database is in an Availability Group.

Can anyone please share with me if that is really the case?

If that is indeed the case, how can I force the garbage collection to delete all the outdated FILESTREAM files, and free up the 400GB space, other than to remove the Availability Group?

Updated with more info (2022-10-17)

The result from DBCC LOGINFO

The database has 105 records from this command. All of them have a Status of 2, which means they are "Active". enter image description here

The result from SELECT log_reuse_wait_desc, name FROM sys.databases WHERE name = DB_NAME()

enter image description here

The result from EXEC sp_spaceused

We are using SQL Server 2014, so the @oneresultset and @include_total_xtp_storage parameters are not supported.

enter image description here

Improve this question
4
  • What does the following queries show in your database? DBCC LOGINFO and SELECT log_reuse_wait_desc, name FROM sys.databases WHERE name = DB_NAME() Also can you please run EXEC sp_spaceused @oneresultset = 1, @include_total_xtp_storage = 1 and add the results of all of these to your post?
    – J.D.
    Oct 14, 2022 at 12:07
  • Filestream garbage collection is, indeed, broken when the database is in an AG
    – AMtwo
    Oct 14, 2022 at 15:59
  • Hi @J.D., thank you for your response. I have added the info in the question
    – L.C. Tan
    Oct 17, 2022 at 0:45
  • Not to be "that guy", but I can't get this to repro (including AMTwo's example) on any mainstream supported versions of SQL Server.
    – Sean Gallardy
    Oct 21, 2022 at 16:35

0

Reset to default

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.