March 9, 2017 at 7:09 am
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
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy