sys.sp_xtp_checkpoint_force_garbage_collection (Transact-SQL)

Artikel
2024-08-23

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';

Dela via