FILESTREAM files being left behind after row deleted

user_95027 0
Viewed 16286

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.

filestream blob sql-server-2008 sql
4 Answers

First you have to create a Checkpoint for the Garbage Collector work. After you Deleted rows you can run this code to eliminate all files that don't belong to any row.

USE [DataBaseName]
GO

-- Create a checkpoint on current database
CHECKPOINT
GO 

-- Execute Garbage Collector after a checkpoint created
EXEC sp_filestream_force_garbage_collection  'DataBaseName'
GO
user_46824910

DELETE FROM tbl_XXX DECLARE @test CHECKPOINT @test = 0

Run this in your sql server and You can observe the file getting deleted from file system also..

You can set the the number of minutes or seconds to wait for the garbage collector to clean up the files from filesystem after performing the deletion operation.

Thanks

haranath

user_2768610
Related