sp_filestream_force_garbage_collection (Transact-SQL)

Applies to: SQL Server

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

A FILESTREAM container can't be removed until all the deleted files within it are cleaned up by the GC. The FILESTREAM GC runs automatically. However, if you need to remove a container before the GC has run, you can use sp_filestream_force_garbage_collection to run the GC manually.

Transact-SQL syntax conventions

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 GC 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 GC on. @filename is optional. If no logical filename is specified, the GC cleans all FILESTREAM containers in the specified database.

Return code values

Value Description
0 Operation success
1 Operation failure

Result set

Value Description
file_name Indicates the FILESTREAM container name
num_collected_items Indicates the number of FILESTREAM items (files or directories) that have been garbage collected (deleted) in this container.
num_marked_for_collection_items Indicates the number of FILESTREAM items (files or directories) that have been marked for GC in this container. These items haven't been deleted yet, but may be eligible for deletion following the GC phase.
num_unprocessed_items Indicates the number of eligible FILESTREAM items (files or directories) that weren't processed for GC in this FILESTREAM container. Items may be unprocessed for various reasons, including:

- Files that need to be pinned down because a log backup or checkpoint hasn't been taken.

- Files in the FULL or BULK_LOGGED recovery model.

- There's a long-running active transaction.

- The replication log reader job hasn't 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.

Remarks

Explicitly runs the FILESTREAM garbage collection task to completion on the requested database (and FILESTREAM container). The GC process removes files that are no longer needed. The time needed for this operation to complete depends on the size of the FILESTREAM data in that database or container, and the amount of DML activity that 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 GC process.

Note

It's 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.

Owing to two-phase operations, the stored procedure should be run twice to actually delete underlying FILESTREAM files.

Garbage collection relies on log truncation. Therefore, if files were deleted recently on a database using the full recovery model, they are garbage-collected only after a log backup of those transaction log portions is taken, and the log portion is marked inactive. On a database using the simple recovery model, a log truncation occurs after a CHECKPOINT has been issued against the database.

Permissions

Requires membership in the db_owner database role.

Examples

The following examples run the GC for FILESTREAM containers in the fsdb database.

A. Specify no container

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

B. Specify a container

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