删除行后留下的FILESTREAM文件 [英] FILESTREAM files being left behind after row deleted

问题描述

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

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.

就物理文件而言，我的意思是SQLServer托管目录中的文件具有唯一标识符作为文件名，而不是添加到dbase的原始文件.

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.

有人知道SQLServer是否最终将删除文件吗?如果从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数据受事务控制，因此不会立即删除.

FILESTREAM data is subject to transaction control and therefore is not deleted instantly.

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

Instead, SQL Server runs a garbage collector which purges the old data when it is sure it had been ultimately deleted.

从 文档 中:

FILESTREAM垃圾回收是一个后台任务，由数据库检查点进程触发.生成足够的事务日志后，将自动运行检查点.有关更多信息，请参见 SQL Server 2008联机丛书主题"CHECKPOINT和日志的活动部分"(

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.

这篇关于删除行后留下的FILESTREAM文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！