sp_filestream_force_garbage_collection (Transact-SQL)


THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Forces the FILESTREAM garbage collector to run, deleting any unneeded FILESTREAM files.

A FILESTREAM container cannot be removed until all the deleted files within it have been cleaned up by the garbage collector. The FILESTREAM garbage collector runs automatically. 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.

Applies to: SQL Server (SQL Server 2012 through current version).
sp_filestream_force_garbage_collection [ @dbname = ]  'database_name'  , @filename = 'logical_file_name' ]  

@dbname = database_name'
Signifies the name of the database to run the garbage collector on.

System_CAPS_ICON_note.jpg Note

dbname is sysname. If not specified, current database is assumed.

@filename = logical_file_name
Specifies the logical name of the FILESTREAM container to run the garbage collector on. @filename is optional. If no logical filename is specified, the garbage collector will clean all FILESTREAM containers in the specified database.

0Operation success
1Operation failure
file_nameIndicates the FILESTREAM container name
num_collected_itemsIndicates the number of FILESTREAM items (files/directories) that have been garbage collected (deleted) in this container.
num_marked_for_collection_itemsIndicates the number of FILESTREAM items (files/directories) that have been marked for garbage collection in this container. These items have not beeeen deleted yet, but may be eligible for deletion the following garbage collection phase.
num_unprocessed_itemsIndicates 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.

Files in the FULL or BULK_LOGGED recovery model.

There is a long-running active transaction.

The replication log reader job has not run.See the white paper FILESTREAM Storage in SQL Server 2008 for more information.
last_collected_xact_seqnoReturns the last corresponding log sequence number (LSN) up to which the files have been garbage collected for the specified FILESTREAM container.

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. 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. 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.

System_CAPS_ICON_note.jpg Note

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.

Requires membership in the db_owner database role.

The following examples run the garbage collector for FILESTREAM containers in the FSDB database.

A. Specifying no container

EXEC sp_filestream_force_garbage_collection @dbname = N'FSDB';  

B. Specifying a container

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

FILESTREAM Storage in SQL Server 2008

Community Additions