FILESTREAM files being left behind after row deleted

Question

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.

Thanks,

Dale

Answer 1

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 CHECKPOINTstatement.

Answer 2

DELETE FROM tbl_XXX DECLARE @test CHECKPOINT @test = 0

Run this in your sql server and You can observe the file getting deleted from file system also..

You can set the the number of minutes or seconds to wait for the garbage collector to clean up the files from filesystem after performing the deletion operation.

Thanks

haranath