SQLite Forum

Release db file
Login

Release db file

(1) By anonymous on 2022-01-15 07:29:47 [link] [source]

How do I release the .db file on Windows 10 & Windows 11 after I close the connection to it? I have written an application and have found that there are a couple of occasions that I need to close the database, delete the file, and recreate the database with data. If that happens before much activity, it works fine, but if there have been some queries and deletions, then the file is locked and can't be deleted or renamed until the app is closed and restarted. What is the solution? Thanks.

(2) By Simon Slavin (slavin) on 2022-01-15 09:26:54 in reply to 1 [link] [source]

You do not have to do anything to 'quit' SQLite to close all the files it has open. If you have closed all your database connections, it should have closed all files and done attendant journal-file cleanup.

However, the sqlite_close() call sometimes doesn't really close the connections. One thing that stops them is statements which have not completed. To make sure this doesn't happen be sure to use sqlite_reset() or sqlite_finalize() on all statements you prepared with sqlite_prepare(). If you never use sqlite_prepare() and used sqlite_exec() instead, you don't have to worry about this.

There are similar problems with unclosed BLOB handlers, backup handlers, and probably others I've forgotten.

Depending on your journal mode, you may be able to figure out whether the connections were closed properly by checking to see if a journal file still exists. This can be convenient because it can be done by a shell script which cannot make SQLite calls. I've sometimes chosen journal modes specifically to allow this.

(Assume '_v2' sprinkled liberally in the above API calls. The above is simplified for brevity.)

(3) By Ryan Smith (cuz) on 2022-01-15 14:16:17 in reply to 1 [link] [source]

Adding to what Simon said, I have done this a few times (made things that create an SQLite DB file and then has to kill/delete the DB).

Few things to keep in mind:

  • It's pretty straight forward - Open the connection, close the connection, delete the file.
  • Avoid journal modes WAL and TRUNCATE for this (else you might have lingering journal files still)
  • Make sure you do not have a second connection or connection pool which is not closed at the time of trying to delete the file
  • Make sure you don't try to "inspect" your work using another external DB viewer (I typically use SQLiteSpeed alongside to see if my program is doing the right things in the DB), but that keeps an open connection to the DB and as long as that exists you cannot delete the file from your program, even if it owns the file.

(4) By AlexJ (CompuRoot) on 2022-01-15 16:38:37 in reply to 1 [link] [source]

That's not sqlite's fault for sure!

That's "normal" Microsoft's behavior when windows kernel locks file between 5-12 minutes for no reason. It happens a lot with other programs, even text files that been recently edited sometimes can't be deleted until kernel release lock on them. There are a lot of utilities exist in a wild to force kernel release file, but I won't recommend to use them blindly since a lot malicious copies exists that replicate MuUnlocker or similar. If you really need to unlock such files, use official Process Explorer run it as administrator and follow for example this guide or simply wait until "Their Majesty Windows" will release lock.

(5) By AlexJ (CompuRoot) on 2022-01-15 16:54:55 in reply to 4 [link] [source]

In addition to said above, DO NOT run programs under administrative account, create standard account and work there. Also, do not tease windows security by keeping database in locations that supposed to be suspicions from windows point of view (many of them, a system ones like Program Files, Windows, some locations in AppData (especially "Temp" folder), ProgramData) are subject for windows for extra supervision. Also adding database location into Antivirus's exclusion list might help (if it isn't system locations). The good place for private database might be a %USERPROFILE%Databases or if you want to hide it from actual user a little, then %LOCALAPPDATA%USERNAME%Databases. If it is a database that need to be accessed from all registered on workstation accounts then it might be a %PUBLIC%Databases. (Both of locations you need to create first.)

(6) By Simon Slavin (slavin) on 2022-01-16 08:15:48 in reply to 4 [source]

Alex's post reminded me of another thing that can mess up file release: antivirus and anti-malware products. These can notice that a file has changed, then immediately open it to inspect it for bad patterns. So instead of your database-changing program which has the file open it might be something else.

Next time it happens use Process Explorer (or whatever it's called in your version of your OS) to see which program has the file open.