SQLite Forum

Reset database
> After 

> > .open mydb.db

> On checking the properties of mydb.db, its size is 0.

That means very little considering that the shell, as a convenience for people who quit the CLI before actually doing anything to the named "database", defers its actual creation until something is done that would modify it.

> I did not want to simply drop the tables and views as I believe that will lead to fragmentation. I am not sure whether VACUUM locks the database.

There can be no fragmentation without content interspersed among free pages. And VACUUM cures it anyway. And who cares if VACUUM locks the database? You would surely not want to "reset" it underneath other connections to it, so being locked would be good then. And if there are no other connections, it will not be locked.

> Setting the file length to zero is faster than ...

Sorry, but that's just crazy. If you do manage to truncate the file to zero length, it will not be a SQLite database any more, not even an empty one.

I'm now fully with Warren on this being an XY problem. Please describe the problem you intend to solve rather than difficulties with solutions you have preconceived.