SQL Server 2008: Filestream Data & Delete Statement

When using Filestream data in SQL Server 2008, you may come across what seems like strange behaviour. This is when you issue a DELETE statement to delete the filestream data, it doesnt actually disappear from the file system.

You may ask…why is this? This is essentially because of SQL Server transaction logging mechanism (under full and bulk logged recovery models) to allow for recovery. The row will appear to be deleted in the database, however the binary data will be removed upon the next checkpoint.

This doesn't happen under the Simple recovery model. To see this for your self, you can setup a small sample with Filestream data. Delete the row and see the file staying around for a while. Then issue the CHECPOINT command and the file will disappear.