Post reply

In-Memory tables and garbage collection of checkpoint files

  • March 9, 2017 at 7:09 am

    #401755

    We're just now setting up some in-memory tables to handle session state since our normal disk-based AspState database is encountering lots of blocking.  Testing has gone well but I noticed more disk space being used by checkpoint files related to the in memory tables.  Apparently in sql 2014 you could force a merge  but not in 2016
    (  In Memory tables   )

    So I threw together a routine, below, to flip the database into full recovery ( enable a log backup ) and do garbage collection which cleans up many/most of those files.   We actually had no intention of backing this database up, at all, but I found these checkpoint files occupying 9GB of space on a VM we didn't think needed much disk space.

    Sample files needing cleanup:
    D:\ASPStateInMemory_xtp\$HKv2

    02/07/2017 08:36 AM   2,097,152 {528A6A35-EA13-43CF-91F6-CFEF7D2938D7}.hkckp
    02/07/2017 08:36 AM   8,388,608 {921AD325-5DDD-44AA-BBBC-FD544F1F3A6C}.hkckp
    02/07/2017 08:36 AM   8,388,608 {46F301CD-B56B-4A6B-94EA-F547446CE50E}.hkckp
    02/07/2017 08:36 AM   16,777,216 {E9CE1E70-F06F-40A0-9667-C46119365B1F}.hkckp

    --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
        order by state_desc

        -- Have checkpoint files been flushed?
        SELECT state_desc,
       file_type_desc,
       count(state_desc) count,
       sum(file_size_in_bytes)/(1024*1024) file_size_in_mb_bytes,
         Avg(file_size_in_bytes)/(1024*1024) Avgfile_size_in_mb_bytes
        FROM sys.dm_db_xtp_checkpoint_files
        GROUP BY state_desc, file_type_desc
        ORDER BY file_size_in_mb_bytes desc

        
    --   garbage collection to clean up files   Backup with init to overwrite since never going to use the BAK
        alter database AspStateInMemory set recovery full
        go
        BACKUP DATABASE [ASPStateInMemory] TO DISK = N'C:\AspStateBackup\ASPStateInMemory_backup_ForGarbageCollection.bak' WITH NOFORMAT, INIT, NAME = N'ASPStateInMemory_backup_2017_03_09_045957_3874827', SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10
       go
        backup log aspstateinMemory to disk='NUL'
        go
        checkpoint
        use ASPStateInMemory
        go
        sys.sp_xtp_checkpoint_force_garbage_collection -----[ @dbname=database_name]

        alter database AspStateInMemory set recovery simple
        go

        

  • March 12, 2017 at 6:32 am

    #1932831

    I probably need to drop and re-create the two in-memory tables so that their durability is just Schema, not Schema and Data since we're ok with data loss for a session state database.

    Also, the job to "cleanup the checkpoint files" caused by having durability delayed, might only need these lines, leaving the db in simple recovery at all times ( no log backups )

    EXECUTE sys.sp_flush_log
        go
    use AspstateInMemory
    go
        sys.sp_xtp_checkpoint_force_garbage_collection -----[ @dbname=database_name]
     go    
    EXEC sp_filestream_force_garbage_collection;
       

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply