SQL SERVER – Slow Filestream Data Cleanup. What Should We Do?

One of my clients reported an issue where they were running low on free disk space on the drive where they have file-stream files. They are one of a heavy user of the filestream feature as compared to any of my previous clients. As per their observation, the filestream disk space was getting released very slow after deleting the records in database tables using this feature.

Based on my search on the internet, it’s the Garbage Collector process in the background which removed the files from the OS when the records if deleted from the table. There are lots of articles on the internet explaining how all that works.

WORKROUND/SOLUTION

If you are running into an issue where space is not getting released even after data is getting deleted from the table and database is highly transactional, here is what you can do.

1. If there is not much of activity on a regular basis, then you can run manual checkpoint in the database.
2. Regular transaction log backups are needed, in case of FULL and BULK_LOGGED recovery model.
3. Make sure there is not a long running transaction in the database.

If both of the above are taken care and still free space is running lower than we can use sp_filestream_force_garbage_collection to force the garbage clean up manually.

Here is the syntax as per documentation:

EXEC sp_filestream_force_garbage_collection @dbname = 'DatabaseNameHere'

My client has put a job in SQL Server agent to run this at the lowest possible frequency.

Reference : Pinal Dave (https://blog.sqlauthority.com)