SQLTeam.com | Weblogs | Forums

How to force garbage collection for Filestream data


#1

I have a database that uses filestream. We write out data to filestream records, and then later delete the records.
But periodic database backups (to disk) that I do seem to keep growing. So I think I need to do something to clean up the deleted records.

I read a lot online about forcing garbage collection but don't understand everything. I'm using SQL Server 2008.

Is it possible to give me the commands I can run to force the garbage collection?
Thanks.


#2

Either rebuild the table or reorganize the index; on reorg, I prefer to explicitly specify LOB_COMPACTION but I believe that's the default now anyway.

ALTER INDEX [index_name] ON dbo.table_name REORGANIZE WITH ( LOB_COMPACTION = ON );