Filestream and FileTable - sp_filestream_force_garbage_collection
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.
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.
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 cleans all FILESTREAM containers in the specified database.
Return Code Values
|file_name||Indicates the FILESTREAM container name|
|num_collected_items||Indicates the number of FILESTREAM items (files/directories) that have been garbage collected (deleted) in this container.|
|num_marked_for_collection_items||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_items||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.
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_seqno||Returns 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.
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.
Due to 2-phase operations, the stored procedure should be run twice to actually delete underlying Filestream files.
Garbage Collection (GC) relies on log truncation. 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. On a database using Simple recovery model, a log truncation occurs after a
CHECKPOINT has been issued against the database.
Requires membership in the db_owner database role.
The following examples run the garbage collector for FILESTREAM containers in the
A. Specifying no container
USE FSDB; GO EXEC sp_filestream_force_garbage_collection @dbname = N'FSDB';
B. Specifying a container
USE FSDB; GO EXEC sp_filestream_force_garbage_collection @dbname = N'FSDB', @filename = N'FSContainer';