Reducing filesize while deleting rows?

I've got a very filesize restricted database. I noticed that when I insert 1000 rows my filesize jumps to 80k, but when I delete all but 50 of those rows...the filesize actually increases to 84k. How do I make sure the filesize of my database shrinks when I delete rows?

Thanks!

Use SqlCeEngine.Compact() or SqlCeEngine.Shrink() to do that. You can find description on MSDN.

|||Unfortunately I'm using native C++ for all this, so I don't have access to SqlCeEngine. Is there a SQL command i could execute to perform the equivalent to a shrink routine? If that doesn't exist I'll need some way to do it while an existing OLEDB connection is open and operating. I need this database to run 24/7 with no downtime whatsoever.

Thanks for the help!
|||

The database connection must be closed to do shrink or repair. This is a design requirement so that database file structure can be recreated.

What you can probably do is to make a copy of the database , compact it, momentarily drop connection to original database and replace original with compacted database. You will have to take a read only lock on original db while the copy is compacted so that no new changes take place.

|||

The OLEDB provider has an Engine object, which has a CompactDatabase method.