FILESTREAM files being left behind after row deleted
blob, filestream, sql, sql-server-2008
Solution
`FILESTREAM` data is subject to transaction control and therefore is not deleted instantly.
Instead, `SQL Server` runs a garbage collector which purges the old data when it is sure it had been ultimately deleted.
From the documentation:
`FILESTREAM` garbage collection is a background task that is triggered by the database checkpoint process. A checkpoint is automatically run when enough transaction log has been generated. For more information, see the SQL Server 2008 Books Online topic “`CHECKPOINT` and the Active Portion of the Log” (http://msdn.microsoft.com/en-us/library/ms189573.aspx). Given that `FILESTREAM` file operations are minimally logged in the database’s transaction log, it may take a while before the number of transaction log records generated triggers a checkpoint process and garbage collection occurs. If this becomes a problem, you can force garbage collection by using the `CHECKPOINT`statement.
Problem
I have successfully set up FILESTREAM on my SQL 2008 server; however I've noticed that even when I have deleted rows containing FILESTREAM data, the physical data file doesn't seem to get deleted. By the physical file, I mean the file in SQLServer's managed directory with a uniqueidentifer as the filename not the original file added to the dbase. Does anyone know if SQLServer will delete the file eventually? If there are a lot of large files removed from the dbase I'd expect to be able to reclaim the space quickly that's all.