Corrupt 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.

--

Ginny Caughey
Device Application Development MVP


wrote in message
news:f42ed61f-bafc-4b80-b1d0-e00fdc12cb81-at-m3g2000hsc.googlegroups.com...
>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.

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.

--

Ginny Caughey
Device Application Development MVP


wrote in message
news:41d17014-318a-4a91-903b-23e930f191d0-at-m45g2000hsb.googlegroups.com...
> 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.

--

Ginny Caughey
Device Application Development MVP


wrote in message
news:4d8b8bff-550e-4cf3-9c73-7d94cb332e95-at-z11g2000prl.googlegroups.com...
> 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.

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.

--

Ginny Caughey
Device Application Development MVP


wrote in message
news:05396fd4-784b-4187-bde4-8ef950da7a24-at-m45g2000hsb.googlegroups.com...
> 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 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.