All things Data Related....

On this site I will be posting entries related to Data platform and analytics that I learn and believe could be valuable learning to others who want insight from their data

SQL Server 2008: Filestream Data & Delete Statement

SQL Server 2008: Filestream Data & Delete Statement

  • Comments 1
  • Likes

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.

Comments
  • Hi
    What about the delete process on the FS, i mean, could you confirm that the delete process treats only 2 objects per second (garbage collector)?
    Or maybe we can force to delete more per second SQL Server 2008 R2 instance!

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment