J Shaddix


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!



Re: Reducing filesize while deleting rows?

Ilya Tumanov


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







Re: Reducing filesize while deleting rows?

J Shaddix

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!





Re: Reducing filesize while deleting rows?

Pragya Agarwal [MSFT]

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.






Re: Reducing filesize while deleting rows?

ErikEJ

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