Condividi tramite

SQL Server 2008: Filestream Data & Delete Statement

  • Articolo

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

  • Anonymous
    May 07, 2015
    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!