Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
sys.sp_xtp_checkpoint_force_garbage_collection
[ [ @dbname = ] 'database_name' ]
[ ; ]
The database to run garbage collection on. The default is the current database. @dbname is sysname.
0
for success. Nonzero for failure.
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. |
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.
Requires membership in the db_owner fixed database role.
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';
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register today