Skip to content

Latest commit

 

History

History
80 lines (56 loc) · 3.72 KB

sys-sp-xtp-checkpoint-force-garbage-collection-transact-sql.md

File metadata and controls

80 lines (56 loc) · 3.72 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sys.sp_xtp_checkpoint_force_garbage_collection (Transact-SQL)
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.
markingmyname
maghan
randolphwest
08/21/2024
sql
system-objects
reference
sys.sp_xtp_checkpoint_force_garbage_collection_TSQL
sys.sp_xtp_checkpoint_force_garbage_collection
sys.sp_xtp_checkpoint_force_garbage_collection
TSQL

sys.sp_xtp_checkpoint_force_garbage_collection (Transact-SQL)

[!INCLUDE sqlserver]

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.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: 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 [!INCLUDE sssql22-md], 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';

Related content