我已经在我的 SQL 2008 服务器上成功设置了 FILESTREAM;但是我注意到,即使我删除了包含 FILESTREAM 数据的行,物理数据文件似乎也没有被删除。
我所说的物理文件是指 SQLServer 托管目录中以唯一标识符作为文件名的文件,而不是添加到数据库中的原始文件。
有谁知道SQLServer最终是否会删除该文件?如果从数据库中删除了很多大文件,我希望能够快速回收空间,仅此而已。
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.
发布评论
评论(4)
FILESTREAM
数据受事务控制,因此不会立即删除。相反,
SQL Server
运行一个垃圾收集器,当确定旧数据最终被删除时,该垃圾收集器会清除旧数据。来自文档:
FILESTREAM
data is subject to transaction control and therefore is not deleted instantly.Instead,
SQL Server
runs a garbage collector which purges the old data when it is sure it had been ultimately deleted.From the documentation:
不幸的是,使用
这只适用于> = SQL Server 2012
use
unfortunately this only works >= SQL Server 2012
首先,您必须创建一个检查点 对于 垃圾收集器工作。删除行后,您可以运行此代码来删除不属于任何行的所有文件。
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.
在您的 SQL Server 中运行它
您还可以观察到文件被从文件系统中删除。
您可以设置执行删除操作后等待垃圾收集器从文件系统中清理文件的分钟或秒数。
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 the filesystem after performing the deletion operation.