sql - 删除行后留下FILESTREAM文件

Question

我已经在SQL 2008服务器上成功设置了FILESTREAM；但是我注意到，即使删除了包含FILESTREAM数据的行，物理数据文件似乎也没有被删除。

物理文件是指SQLServer托管目录中的文件，其文件名带有uniqueidentifer，而不是添加到dbase的原始文件。

有谁知道SQLServer是否最终将删除该文件？如果从dbase中删除了很多大文件，我希望能够快速回收空间。

Answer 1

FILESTREAM数据受事务控制，因此不会立即删除。

相反，SQL Server运行一个垃圾回收器，当确定已最终删除旧数据时，它将清除旧数据。

从documentation:

FILESTREAM garbage 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 “CHECKPOINT and the Active Portion of the Log” (http://msdn.microsoft.com/en-us/library/ms189573.aspx). Given that FILESTREAM file 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 the CHECKPOINTstatement.