SQL SERVER – Slow Filestream Data Cleanup. What Should We Do?

Slow Filestream Data Cleanup can follow deletion when files are still ineligible for garbage collection. One customer was running out of space.

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases WHERE name = N'YourFilestreamDatabase';
-- Targeted administration only, after identifying why files remain pinned:
-- EXEC sys.sp_filestream_force_garbage_collection
--     @dbname = N'YourFilestreamDatabase', @filename = N'YourContainer';

The background collector normally handles eligible files. Check log truncation first. Full and bulk-logged recovery require relevant log backups, while simple recovery uses checkpoints. Long transactions and replication log-reader dependencies can keep files pinned.

The procedure can target one container instead of scanning every container. Review collected, marked-for-collection and unprocessed counts. Microsoft documents two invocations for the two-phase cleanup. Calling it doesn’t bypass dependencies holding files in use.

This customer added a low-frequency Agent job. That workaround isn’t a reason to force cleanup frequently everywhere. The procedure can cause I/O, so schedule it appropriately when needed. Never delete FILESTREAM container files manually in Explorer.

Reference: FILESTREAM eligibility and two-phase collection.

Forced collection is not a bypass for active dependencies, it is a way to process eligible FILESTREAM files.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.