Forcing garbage collection with in memory oltp
Posted: September 27, 2015 Filed under: Microsoft SQL Server, Microsoft SQL Server 2014, Microsoft SQL Server 2016 | Tags: Microsoft SQL Server, Microsoft SQL Server 2014, Microsoft SQL Server 2016 Leave a commentTo force manual garbage collection:
- First we check the status of the checkpoint files:
select container_id,state_desc,lower_bound_tsn,upper_bound_tsn from sys.dm_db_xtp_checkpoint_files
Anything that is MERGE TARGET is pending a merge.
In SQL Server 2014 we can run:
sys.sp_xtp_merge_checkpoint_files database_name, @transaction_lower_bound, @transaction_upper_bound
to force a merge, manual merges are not possible in SQL Server 2016!
2. Once the merge is complete we have to run a checkpoint to ensure everything is persisted on disk.
3. Next we need to run a log backup otherwise dm_db_xtp_checkpoint_files.state_desc would be ‘WAITING FOR LOG TRUNCATION’
4. Finally we can force garbage collection using
sys.sp_xtp_checkpoint_force_garbage_collection [[ @dbname=database_name]