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.
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:
FILESTREAMgarbage 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 “CHECKPOINTand the Active Portion of the Log” (http://msdn.microsoft.com/en-us/library/ms189573.aspx). Given thatFILESTREAMfile 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 theCHECKPOINTstatement.