 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server CE / August 2008Corrupt database
I have a Windows program developed in VS2005 using SqlServer CE 3.1. Because of speed, I keep a shared static connection open to a database that is frequently read. During periods of inactivity, a new copy of the database is copied over writing the previous copy. I first call CloseSharedConnections, wait 10 seconds and then over write the file. Frequently, the database becomes corrupt, but when I try to compact it it fails on a sharing violation. I then recover the backup copy of the database which is successful. Filemon does not show any outstanding locks and I have no problem over writing the new database with the backup copy. Can anyone suggest anything to help with the corrupt database files. It sounds like there are a couple of issues here, but the main one is why is the database becoming corrupt? I suspect the problem may be the shared connection you're using. Are you accessing the database on multiple threads from the single connection? This scenario is definitely not supported. Instead, keep a single master connection open for performance reasons, but open and close other connections for each thread as needed. A second thing you can try after you make sure that only a single thread is using each connection is to change the flush interval (on the connection string) so writes to the database aren't buffered.  SignatureGinny Caughey Device Application Development MVP >I have a Windows program developed in VS2005 using SqlServer CE 3.1. > Because of speed, I keep a shared static connection open to a database [quoted text clipped - 7 lines] > with the backup copy. Can anyone suggest anything to help with the > corrupt database files. The majority of the time(98%) it is straight reads on the database with only the one thread. I have checked with the users when we have gotten the errors and they said they weren't updating. I have tried this myself updating each time and can only re-create the problem occasionally. I will try changing the connection string however to see if it helps. Thanks. On those rare occasions when you have been able to reproduce the problem, were you attempting to update from another thread? I'm still not clear how your app works. It would be nice if changing the connection string solves the issue, but I'd feel more comfortable if we knew what the cause really is. SignatureGinny Caughey Device Application Development MVP > The majority of the time(98%) it is straight reads on the database > with only the one thread. I have checked with the users when we have > gotten the errors and they said they weren't updating. I have tried > this myself updating each time and can only re-create the problem > occasionally. I will try changing the connection string however to see > if it helps. Thanks. The program is a high volume entry program. It will do 60,000 to 80,000 reads on this database in an hour.The update is using a thread to not interrupt the flow. I have been suspecting this is the problem, but the users tell me they haven't always been updating.The only time I've been able to recreate the problem is when I was updating, but even then it doesn't always happen. I have changed the connection string on one PC as a test, but I would be surprised if this solves the problem. My next change would be change the update to not run in a thread. I have searched through the code a number of times and can't find any open connections or locks on the file. I think you're right about what's happening. Removing the worker thread is a good next step. SignatureGinny Caughey Device Application Development MVP > The program is a high volume entry program. It will do 60,000 to > 80,000 reads on this database in an hour.The update is using a thread [quoted text clipped - 6 lines] > thread. I have searched through the code a number of times and can't > find any open connections or locks on the file. Removing the thread didn't help. Here is my update code. I pass a string with the update query. int ReturnValue = 0; OpenMaster();//Check for shared connection SqlCeCommand cmd = new SqlCeCommand(sql); try { ReturnValue = MasterDatabase.ExecuteNonQuery(cmd); } catch (SqlCeException ex) { ReturnValue = ex.NativeError; } finally { cmd.Dispose(); } return ReturnValue; Is there something I should be doing differently here. After I copy over a new database, I call Verify on the database, which if it fails, I try Compact. This is where it fails on a sharing violation. I'm not seeing anything necessarily wrong here - perhaps somebody else will spot something. For performance reasons you'd do better caching your update statement and just refreshing the SQL parameters. You'd also get better performance using SqlCeResultSet than a SQL statement. I think at this point I'd just experiment with different update techniques and see if there's some other approach that prevents the problem. SignatureGinny Caughey Device Application Development MVP > Removing the thread didn't help. Here is my update code. > I pass a string with the update query. [quoted text clipped - 23 lines] > if it fails, I try Compact. This is where it fails on a sharing > violation. I don't see anything unusual either. I'm curious about this statement in your first post: "During periods of inactivity, a new copy of the database is copied over writing the previous copy" Can you explain in more detail how you're overwriting/copying the database? I'm also curious as to why this is done. Doug The database contains master detail which is updated on another server during the day. New masters are added and others changed and they want the entry stations to have the most up to date data. They don't want the operators to have to exit out of program.There is a windows service that runs every 2 hours to strip the data into an sqlserver ce database on a shared location. When the screen saver on the PC is activated it triggers the entry program to copy a new database from the shared location. Originally I was using File.Copy but changed it to use filestream to copy to see if that would help. After the copy I call verify to check for corruption. When I try to compact it, it fails because it says the file is locked, however when I copy the backup over it, it copies without any locking errors. I'm thinking that the corruption occurs when you are copying the file, not from your internal transactions. Perhaps a connection is being attempted before the file is completely copied. Maybe I'm not completely understanding your system, but why wouldn't you just update/add/delete the necessary records in your existing database instead of comletely overwriting the file? Doug We didn't do the update because we will have a minimum of 30 PCs updating at the same time over the network and we were concerned about the time and locking issues, however it may be one of the ideas to consider if all else fails. There would be no connection during the error. There are no other processes accessing the database at the time. I agree with Doug - using SqlCE inserts/updates/deletes makes more sense to me too. You might want to look at Sync Services for ADO.NET for Devices: http://www.microsoft.com/downloads/details.aspx?FamilyId=75FEF59F-1B5E-49BC-A21A -9EF4F34DE6FC&displaylang=en SignatureGinny Caughey Device Application Development MVP > We didn't do the update because we will have a minimum of 30 PCs > updating at the same time over the network and we were concerned about > the time and locking issues, however it may be one of the ideas to > consider if all else fails. There would be no connection during the > error. There are no other processes accessing the database at the > time. We'll look into it. Thanks. It still seems to me that the most likely source of the corruption is in that copy process. Try copying the file to the device under a "temporary" name that your app won't find. Then once it's completely copied, rename it. Also, are you certain that the file you're copying isn't corrupt to start with? Interesting idea. Would be quite simple to try. I am sure the file I am copying is OK. I compact it when I create it and when I test I usually can copy it without trouble. I also know that first thing in the morning when the users login they get a new file that works every time. I also know that if they exit out and log back in they get a new file no problem. It does sound like an open connection problem. I am also getting another set of eyes here to look for an open connection I am not seeing. Does CloseSharedConnections not close any open connections?? I think you'd be safer closing each connection explicitly yourself. You could also check after doing that whether the connection's State is Open after that as a sanity check. SignatureGinny Caughey Device Application Development MVP > Interesting idea. Would be quite simple to try. I am sure the file I > am copying is OK. I compact it when I create it and when I test I [quoted text clipped - 5 lines] > to look for an open connection I am not seeing. Does > CloseSharedConnections not close any open connections?? I sheepishly reply this morning to say after the 20th search through my code I found an open local connection in a method I didn't think was being used.If it had teeth it would have bit me. Thank you all for your time and suggestions. I will be implementing your suggestions on speed. Unfortunately, our main database is not SQLServer, so i don't think we could easily implement Sync Services. I will put my changes in production tonight and hopefully you won't hear from me again. lol... I think we've all been there. Doug |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.