See http://blogs.msdn.com/b/psssql/archive/2008/01/15/how-it-works-file-stream-the-before-and-after-image-of-a-file.aspx.

It makes sense: SQL Server uses the concepts of “before image” and “after image”. The “after image” should contain the varbinary(max) column content after the insert/update statement. If the transaction succeeds, the “after image” becomes the current data. The “before image” is recorded in a special table, named “tombstone”; a garbage collector process will clear this image.

This GC process does not execute immediately after the last transaction commit.

The current published information is: “a FS garbage collection executes at a CHECKPOINT”.

There are some events that automatically trigger the GC, but they occur only in special contexts (log file active portion exceeds a certain size, log file is over 70% full and the database is in log-truncate mode) that do not appear during standard operations.

Conclusion : if you want to explicitly trigger a garbage collection, issue a CHECKPOINT statement (http://msdn.microsoft.com/en-us/library/ms188748.aspx), backup  the database or stop SQL Server. There is a chance this will not delete all tombstoned FS entries (see http://blogs.msdn.com/b/psssql/archive/2011/06/23/how-it-works-filestream-rsfx-garbage-collection.aspx), so be prepared to have some unusable disk space when working with FS storage, especially if you often update the varbinary(max) columns.

Advertisements