How to make Extensible Storage Engine Database Shrink? - the database should not have much data inside?

Question

Hoping Laurion Burchall is listening :)

My database has a longbinary field that is used to temporarily cache some data. The data may not actually be needed for very long. Typical sizes range from 1k to 3MB

I've noticed the size of the database seems to grow without bound, even though at any point in time there is under 20 or 30MB of actual data that should be in the database.

The patten I'm using is

a) add the data with JetSetColumn, say 3MB
b) ... wait until the data is no longer need, often just a few hours - sometimes a little longer
c) when I want to delete the data, I'm (this could be wrong) using JetSetColumns passing a value of NULL. 

I would think the database would tend to be around 30MB or so, but instead it just gets bigger and bigger (30GB). Either the delete I'm doing isnt correct or I need to compact somehow?

I'm hoping someone would clearify what's happening, if I'm using ESE incorrectly, etc

Answer 1

ESE doesn't reuse logically deleted/overwritten records until told to do so with JetDefragment or JetCompact