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.
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.
- If there is not much of activity on a regular basis, then you can run manual checkpoint in the database.
- Regular transaction log backups are needed, in case of FULL and BULK_LOGGED recovery model.
- 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)
Garbage Collection (GC) relies on log truncation. Therefore, if files were deleted recently on a database using Full Recovery model, they are GC-ed only after a log backup of those transaction log portions is taken and the log portion is marked inactive. On a database using Simple recovery model, a log truncation occurs after a CHECKPOINT has been issued against the database.
My client was having regular log backups but still it was slow.
This was useful Pinal, Thank you
we tried this on one of our server and then after that we starting getting error as General error HY000 (0)–[Microsoft][ODBC Driver 13 for SQL Server]Protocol error in TDS stream for applications intermittently