sp_filestream_force_garbage_collection (Transact-SQL)sp_filestream_force_garbage_collection (Transact-SQL)

适用对象: 是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

强制运行 FILESTREAM 垃圾回收器,从而删除任何不需要的 FILESTREAM 文件。Forces the FILESTREAM garbage collector to run, deleting any unneeded FILESTREAM files.

在垃圾回收器清除 FILESTREAM 容器中的所有删除文件后,才能删除该容器。A FILESTREAM container cannot be removed until all the deleted files within it have been cleaned up by the garbage collector. FILESTREAM 垃圾回收器是自动运行的。The FILESTREAM garbage collector runs automatically. 但是,如果你需要删除容器之前垃圾回收器运行之后,可以使用 sp_filestream_force_garbage_collection 手动运行垃圾回收器。However, if you need to remove a container before the garbage collector has run, you can use sp_filestream_force_garbage_collection to run the garbage collector manually.

语法Syntax

sp_filestream_force_garbage_collection
    [ @dbname = ]  'database_name'
    [ , [ @filename = ] 'logical_file_name' ]

参数Arguments

[ @dbname = ] 'database_name'
指示要运行垃圾回收器的数据库的名称。Signifies the name of the database to run the garbage collector on.

备注

@dbnamesysname@dbname is sysname. 如果未指定,则假定为当前数据库。If not specified, current database is assumed.

[ @filename = ] 'logical_file_name'
指定要运行垃圾回收器的 FILESTREAM 容器的逻辑名称。Specifies the logical name of the FILESTREAM container to run the garbage collector on. @filename 是可选的。@filename is optional. 如果不指定任何逻辑文件名,垃圾回收器清除指定的数据库中的所有 FILESTREAM 容器。If no logical filename is specified, the garbage collector cleans all FILESTREAM containers in the specified database.

返回代码值Return Code Values

ReplTest1Value DescriptionDescription
00 操作成功Operation success
11 操作失败Operation failure

结果集Result Sets

ReplTest1Value 描述Description
file_namefile_name 指示 FILESTREAM 容器名称Indicates the FILESTREAM container name
num_collected_itemsnum_collected_items 指示此容器中已回收(删除)的 FILESTREAM 项目(文件/目录)数。Indicates the number of FILESTREAM items (files/directories) that have been garbage collected (deleted) in this container.
num_marked_for_collection_itemsnum_marked_for_collection_items 指示此容器中已标记为回收(删除)的 FILESTREAM 项目(文件/目录)数。Indicates the number of FILESTREAM items (files/directories) that have been marked for garbage collection in this container. 这些项,尚未删除,但可能是可用于删除以下垃圾收集阶段。These items have not been deleted yet, but may be eligible for deletion following the garbage collection phase.
num_unprocessed_itemsnum_unprocessed_items 指示此 FILESTREAM 容器中符合条件但未进行垃圾回收处理的 FILESTREAM 项目(文件或目录)数。Indicates the number of eligible FILESTREAM items (files or directories) that were not processed for garbage collection in this FILESTREAM container. 可能由于各种原因而未处理项目,其中包括:Items may be unprocessed for various reasons, including the following:

由于尚未执行日志备份或检查点操作,需要暂时锁定文件。Files that need to be pinned down because Log backup or CheckPoint has not been taken.

文件处于 FULL 或 BULK_LOGGED 恢复模式。Files in the FULL or BULK_LOGGED recovery model.

有长时间运行的活动事务。There is a long-running active transaction.

复制日志读取器作业未运行。The replication log reader job has not run. 请参阅白皮书SQL Server 2008 中的 FILESTREAM 存储有关详细信息。See the white paper FILESTREAM Storage in SQL Server 2008 for more information.
last_collected_xact_seqnolast_collected_xact_seqno 返回最后一个相应的日志序列号 (LSN),已垃圾回收指定 FILESTREAM 容器中小于该编号的文件。Returns the last corresponding log sequence number (LSN) up to which the files have been garbage collected for the specified FILESTREAM container.

备注Remarks

在请求的数据库(和 FILESTREAM 容器)上显式运行 FILESTREAM 垃圾回收器任务,直至完成。Explicitly runs FILESTREAM Garbage Collector task to completion on the requested database (and FILESTREAM container). 垃圾回收进程将删除不再需要的文件。Files that are no longer needed are removed by the garbage collection process. 完成此操作所需的时间取决于该数据库或容器中的 FILESTREAM 数据大小,以及最近对 FILESTREAM 数据执行的 DML 活动的数量。The time needed for this operation to complete depends on the size of the FILESTREAM data in that database or container as well as the amount of DML activity that has recently occurred on the FILESTREAM data. 尽管可以在数据库联机时运行此操作,但这可能会在运行期间由于垃圾回收进程执行的各种 I/O 活动而影响数据库性能。Though this operation can be run with the database online, this may affect the performance of the database during its run due to various I/O activities done by the garbage collection process.

备注

建议仅在必要时运行此操作,并且在通常的工作时间以外运行。It is recommended that this operation be run only when necessary and outside usual operation hours.

只能在单独容器或单独数据库上同时运行针对此存储过程的多个调用。Multiple invocations of this stored procedure can be run simultaneously only on separate containers or separate databases.

由于 2 阶段操作,应运行存储的过程两次以实际删除基础 Filestream 文件。Due to 2-phase operations, the stored procedure should be run twice to actually delete underlying Filestream files.

垃圾回收 (GC) 依赖于日志截断。Garbage Collection (GC) relies on log truncation. 因此,如果使用完整恢复模式的数据库上,最近删除了文件,它们是 GC ed 仅在执行这些事务日志部分的日志备份和日志部分标记为不活动之后。Therefore, if files were deleted recently on a database using Full Recovery model, they are GC-ed only after a log backup of those transaction log portions is taken and the log portion is marked inactive. 在使用简单恢复模式的数据库之后, 将发生日志截断CHECKPOINT发出对数据库。On a database using Simple recovery model, a log truncation occurs after a CHECKPOINT has been issued against the database.

权限Permissions

需要 db_owner 数据库角色中的成员身份。Requires membership in the db_owner database role.

示例Examples

以下示例为 FSDB 数据库中的 FILESTREAM 容器运行垃圾回收器。The following examples run the garbage collector for FILESTREAM containers in the FSDB database.

A.A. 不指定任何容器Specifying no container

USE FSDB;  
GO  
EXEC sp_filestream_force_garbage_collection @dbname = N'FSDB';  

B.B. 指定一个容器Specifying a container

USE FSDB;  
GO  
EXEC sp_filestream_force_garbage_collection @dbname = N'FSDB',
    @filename = N'FSContainer';  

请参阅See Also

FilestreamFilestream
FiletableFiletables
Filestream 和 FileTable 动态管理视图 (Transact-SQL)Filestream and FileTable Dynamic Management Views (Transact-SQL)
Filestream 和 FileTable 目录视图 (Transact-SQL)Filestream and FileTable Catalog Views (Transact-SQL)
sp_kill_filestream_non_transacted_handles (Transact-SQL)sp_kill_filestream_non_transacted_handles (Transact-SQL)