I might missed that but: what are you trying to
acomplish by using explorer to copy the
database file ?
I guess you are doing it for backup purpose.
For this, be aware that it might be dangerous to
do a simple file copy on a running database since
you might forget some temporary files used by
sqlite - in particular the journal file.
As an other reply allready mentioned, you should
try to put an exclusive transaction around the
file copy. However, as you found this will lock
the file seen by external process but the file
is not locked for your application itselve.
I have a similar approach in my application to
backup the database on a daily basis:
pseudocode:
DB.Execute("BEGIN EXCLUSIVE TRANSACTION;");
CopyFile(DB.SqlFile, BackUpFile, FALSE);
DB.Execute("COMMIT;");By doing so inside the application, I don't see any particular memory increase for one week now but my db file is not as big as yours. I would be curious if that still increase memory usage in your case... HTH Marcus > On Tue, Jul 7, 2009 at 1:10 PM, Mark Spiegel<mspie...@vipmail.com> wrote: > >> This should give you a picture of who is opening the file and with what >> flags. In particular, how is your SQLite app and Explorer opening the >> file? > > Thanks for the pointer to Filemon; I'm always happy to learn about a > new tool. Apparently its functionality has been integrated into > Procmon, so that's what I used to follow your debugging steps. > > To address some of the points you mention earlier: > > * I'm running as Administrator, and I can see SYSTEM-owned processes > in Task Manager. Not sure if that means I can see all processes or > not, but I suspect that I can. > > * I was actually wrong about VM going up. In fact, the Pagefile > doesn't seem to grow much. Physical Memory (as reported by Resource > Monitor) is what's growing. Once it peaks (at around 99%) the page > fault rate goes through the roof. > > * When this situation occurs, the system is most definitely not > CPU-bound, as the CPU usage hovers around 1%. The high page fault rate > is what seems to be making other applications unresponsive. > > * Working Set Size and Peak Working Set size of all processes in Task > Manager are very modest; the memory appears to be going to some sort > of caching mechanism in the OS. > > Using procmon, I filtered on "Path" for my particular sqlite file and > repeated my experiments from the original post. I verified that > sqlite3.exe and Explorer.EXE are the only processes opening the > file.The thing that stands out in the pathological cases is the > presence of SHARING VIOLATION result code. Whichever one of sqlite or > Explorer attempts to open the file second will throw this error, and > that's when I see physical memory begin to grow. > > Here's a trace of sqlite accessing the file, then Explorer copying it > (CSV format): > > "Time of Day","Process Name","PID","Operation","Path","Result","Detail" > "6:01:46.1439881 > PM","sqlite3.exe","11088","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SUCCESS","Desired > Access: Read Attributes, Disposition: Open, Options: Open For Backup, > Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, > AllocationSize: n/a, Impersonating: TESTBED9\Administrator, > OpenResult: Opened" > "6:01:46.1446133 > PM","sqlite3.exe","11088","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SUCCESS","Desired > Access: Read Attributes, Disposition: Open, Options: Open For Backup, > Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, > AllocationSize: n/a, Impersonating: TESTBED9\Administrator, > OpenResult: Opened" > "6:01:46.1448474 > PM","sqlite3.exe","11088","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SUCCESS","Desired > Access: Generic Read/Write, Disposition: OpenIf, Options: Synchronous > IO Non-Alert, Non-Directory File, Random Access, Attributes: N, > ShareMode: Read, Write, AllocationSize: 0, Impersonating: > TESTBED9\Administrator, OpenResult: Opened" > "6:01:56.1737399 > PM","Explorer.EXE","3272","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SHARING > VIOLATION","Desired Access: Generic Read, Write Data/Add File, > Disposition: Open, Options: Sequential Access, Synchronous IO > Non-Alert, Non-Directory File, Attributes: n/a, ShareMode: Read, > AllocationSize: n/a" > "6:01:56.1738486 > PM","Explorer.EXE","3272","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SUCCESS","Desired > Access: Generic Read, Write Data/Add File, Disposition: Open, Options: > Sequential Access, Synchronous IO Non-Alert, Non-Directory File, > Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, > OpenResult: Opened" > > And Explorer copying the file then sqlite3 opening it: > > "6:30:23.1071085 > PM","Explorer.EXE","3272","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SUCCESS","Desired > Access: Generic Read, Write Data/Add File, Disposition: Open, Options: > Sequential Access, Synchronous IO Non-Alert, Non-Directory File, > Attributes: n/a, ShareMode: Read, AllocationSize: n/a, OpenResult: > Opened" > "6:30:41.0615955 > PM","sqlite3.exe","1988","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SUCCESS","Desired > Access: Read Attributes, Disposition: Open, Options: Open For Backup, > Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, > AllocationSize: n/a, Impersonating: TESTBED9\Administrator, > OpenResult: Opened" > "6:30:41.2108393 > PM","sqlite3.exe","1988","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SUCCESS","Desired > Access: Read Attributes, Disposition: Open, Options: Open For Backup, > Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, > AllocationSize: n/a, Impersonating: TESTBED9\Administrator, > OpenResult: Opened" > "6:30:41.2687134 > PM","sqlite3.exe","1988","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SHARING > VIOLATION","Desired Access: Generic Read/Write, Disposition: OpenIf, > Options: Synchronous IO Non-Alert, Non-Directory File, Random Access, > Attributes: N, ShareMode: Read, Write, AllocationSize: 0, > Impersonating: TESTBED9\Administrator" > "6:30:41.2837648 > PM","sqlite3.exe","1988","IRP_MJ_CREATE","C:\inetpub\wwwroot\Vivisimo\bin\fake-cache.sqlt","SUCCESS","Desired > Access: Generic Read, Disposition: OpenIf, Options: Synchronous IO > Non-Alert, Non-Directory File, Random Access, Attributes: N, > ShareMode: Read, Write, AllocationSize: 0, Impersonating: > TESTBED9\Administrator, OpenResult: Opened" > > > Just to be clear, I think Windows is really the one that is at fault > here; the behavior seems very broken to me. Unfortunately, I need the > app to work on Windows without this happening, so I need to figure out > some kind of workaround in a.) Windows settings b.) the sqlite source > or c.) how my app is using sqlite. > > Thanks for your help, > -Stan > > >> >> Filemon can generate a painful amount of output, but it may be worth it >> to see what is going on. >> >> Do you have task manager set to show processes from all users? >> >> When you say "huge amount of virtual memory", what exactly do you mean? >> What statistic in task manager are you referring to? >> >> When you get in this situation, what process is using the most CPU? Is >> the system CPU bound or I/O bound? >> >> Don't forget the perfmon tool. It can be helpful in figuring out these >> kinds of problems as well. >> >> When you say the system is sluggish, does that mean the mouse is >> sluggish? Apps won't start? IE is unresponsive? Can you be a little >> more specific? Note that when copying or even using big files like >> this, you can overrun the cache manager. It will be filled with data >> from your file forcing out everything else of use. It can take awhile >> for the system to recover from this. Some AV products will create this >> situation when they do a system wide scan for example. >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
