Edit

sys.sp_xtp_force_gc (Transact-SQL)

  • Article

Applies to: SQL Server

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.

In cases where a large volume of in-memory data has been released, and where the memory isn't soon be needed for other in-memory data, this procedure can free up memory for other uses. If you anticipate the memory being used soon for other in-memory data, freeing it here would only cause extra overhead, as it would need to be reallocated for the new data.

For more information on Memory-optimized TempDB metadata out of memory errors, see Memory-optimized tempdb metadata (HkTempDB) out of memory errors.

The sys.sp_xtp_force_gc system stored procedure was introduced in SQL Server 2022 (16.x) CU 1 and SQL Server 2019 (15.x) CU 13. This stored procedure isn't currently supported on Azure SQL Database and Azure SQL Managed Instance.

Transact-SQL syntax conventions

Syntax

sys.sp_xtp_force_gc
    [ [ @dbname = ] 'database_name' ]
[ ; ]

Arguments

[ @dbname = ] 'database_name'

The database to release unused memory for memory-optimized tables. @dbname is sysname.

  • When the @dname parameter isn't specified, only system-level memory structures in the instance are considered.

  • When the @dname parameter provided is tempdb, the memory structures related to memory-optimized tempdb metadata are affected.

  • When the @dname parameter provided is a user database, the memory structures related memory-optimized tables are affected.

Therefore, you may expect to see different results when executing sys.sp_xtp_force_gc: without a parameter, with @dbname = N'tempdb', or with @dbname = a user database name.

Return code values

0 for success. Nonzero for failure.

Permissions

Requires membership in the db_owner fixed database role.

Remarks

Memory-optimized garbage collection happens normally and automatically in response to memory pressure. You can manually trigger garbage collection with 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.

Contrast with sys.sp_xtp_checkpoint_force_garbage_collection, which marks checkpoint 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.

Prior to SQL Server 2022 (16.x), execute this stored procedure twice.

Examples

To execute garbage cleanup on system-level memory structures and Memory-optimized TempDB metadata in SQL Server 2022 (16.x):

EXEC sys.sp_xtp_force_gc N'tempdb';
GO

EXEC sys.sp_xtp_force_gc;
GO

To execute garbage cleanup on system-level memory structures and Memory-optimized TempDB metadata prior to SQL Server 2022 (16.x):

EXEC sys.sp_xtp_force_gc N'tempdb';
GO

EXEC sys.sp_xtp_force_gc N'tempdb';
GO

EXEC sys.sp_xtp_force_gc;
GO

EXEC sys.sp_xtp_force_gc;
GO

Related content