sys.sp_xtp_checkpoint_force_garbage_collection (Transact-SQL)
Applies to: 
SQL Server
Marks source files used in the merge operation with the log sequence number (LSN) after which they aren't needed and can be garbage collected. Also, sys.sp_xtp_checkpoint_force_garbage_collection moves the files whose associated LSN is lower than the log truncation point to FILESTREAM garbage collection.
Contrast with sys.sp_xtp_force_gc, which causes the in-memory engine to release memory related to deleted rows of in-memory data that are eligible for garbage collection, which haven't yet been released by the process.
Transact-SQL syntax conventions
Syntax
sys.sp_xtp_checkpoint_force_garbage_collection
[ [ @dbname = ] 'database_name' ]
[ ; ]
Arguments
[ @dbname = ] 'database_name'
The database to run garbage collection on. The default is the current database. @dbname is sysname.
Return code values
0 for success. Nonzero for failure.
Result set
A returned row contains the following information:
| Column | Description |
|---|---|
num_collected_items |
Indicates the number of files that have been moved to FILESTREAM garbage collection. The log sequence number (LSN) of these files is less than the LSN of log truncation point. |
num_marked_for_collection_items |
Indicates the number of data/delta files whose LSN has been updated with the log blockID of the end-of-log LSN. |
last_collected_xact_seqno |
Returns the last corresponding LSN up to which the files have been moved to FILESTREAM garbage collection. |
Remarks
You can manually trigger garbage collection with another system stored procedure, sys.sp_xtp_force_gc. You can observe the reduction in memory cleanup in sys.dm_xtp_system_memory_consumers.
In SQL Server 2022 (16.x), the sys.dm_xtp_system_memory_consumers dynamic management view has improved insights specific to Memory-optimized TempDB metadata.
Permissions
Requires membership in the db_owner fixed database role.
Examples
To mark unneeded source files for garbage collection in the tempdb database, use the following sample script:
EXEC sys.sp_xtp_checkpoint_force_garbage_collection N'tempdb';