asked
How is FileStream Cleanup Done?
Hi, Using MSSQL 2012, I am just learning about FileStream and FileTable and have been experimenting with them and see some un-expected behavior and wondering what I'm missing. For now, lets talk about filestream only. I created a filestream column successfully. In the same table I have another varbinary(max) column that contains actual image binary data. So, I copied the data from that field to the new filestream field. That was successful and comparing the data, they are both the same. I navigated to the filesystem's physical location of the filestream data and its all there (31,100 files) as expected. Now the problem... When I delete 1000 rows the folder size of the physical location does not change, nore does the qty of files it contains which was the same as the row count, plus 1 or 2. Same for updating the filestream column by setting it to null, no change to the physical filesystem structure. I also done a full backup and shrink operations and neither affected the filesystem structure. Note the filestream column contains an exact copy of the other varbinary(max) column which are actual image files. They both are not tied to any filesystem file such as "c:\something.jpg". The examples I've seen concerning filestream is coping files from the filesystem into them, instead I copied binary data from the other field. So, at this point I have a bunch of orphaned filesystem files that sql server created and now that all the rows are deleted, I expected those files be deleted as well. So, how do I solve this? Thanks, Stanley
It is quite complicated problem
Hi, So, being complicated??? Are we to avoid filestream features? Can you elaborate on why its complicated as all the docs I've read makes it seem straight forward and pretty much compatible with all the other features such as backup/recovery and t-sql commands. The docs are saying that its pretty much the same except the binary data lives on the filesystem instead of inside the database. What am I missing here? Also note that my recovery model is SIMPLE... Does that matter? Thanks, Stanley
This link states that the underlying file clean up is done by the garabage collector.
https://msdn.microsoft.com/en-us/library/cc645962.aspx Furthermore, Paul Randal discusses this here:
http://www.sqlskills.com/blogs/paul/filestream-garbage-collection/ Hope that helps!
19 People are following this question.
