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

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions)

強制執行 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.


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


[ @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

Value 描述Description
00 作業成功Operation success
11 作業失敗Operation failure

結果集Result Sets

Value 描述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 傳回指定 FILESTREAM 容器中已進行記憶體回收之檔案的最後一個對應記錄序號 (LSN)。Returns the last corresponding log sequence number (LSN) up to which the files have been garbage collected for the specified FILESTREAM container.


在要求的資料庫 (和 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。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. 在使用簡單復原模式的資料庫上,會在針對資料庫發出的之後,發生記錄截斷 CHECKPOINTOn a database using Simple recovery model, a log truncation occurs after a CHECKPOINT has been issued against the database.


需要 db_owner 資料庫角色中的成員資格。Requires membership in the db_owner database role.


下列範例會針對 FSDB 資料庫中的 FILESTREAM 容器執行記憶體回收行程。The following examples run the garbage collector for FILESTREAM containers in the FSDB database.

A.A. 未指定容器Specifying no container

EXEC sp_filestream_force_garbage_collection @dbname = N'FSDB';  

B.B. 指定容器Specifying a container

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

另請參閱See Also

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)