Menu
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.
334
The FileStream feature stores unstructured data in the file system and keeps a pointer of the data in the database whereas FileTable extends this feature even further to allow non-transactional access (the ability to access files without prior authorization from the Database Engine from the shared location).
The isolation semantics are governed by Database Engine transaction isolation levels. Read-committed isolation level is supported for Transact-SQL and file system access. Repeatable read operations, serializable and snapshot isolation levels are supported. Dirty read isn't supported.
To enable and change FILESTREAM settingsRight-click the instance, and then click Properties. In the SQL Server Properties dialog box, click the FILESTREAM tab. Select the Enable FILESTREAM for Transact-SQL access check box.
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.
140
use
sp_filestream_force_garbage_collection unfortunately this only works >= SQL Server 2012
40
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
