Database corrupt on power loss

We are planning to use SQL Compact for an industrial control application.

We selected this database based on the assuption that it will survive when power is lost in the middle of a transaction.

We setup a test where we open a connection, write to database and randomly shut of the power while writing to database.

We are running on XPE with EWF enabled for C drive. Database is on D drive.

We cycle the power every ~ 20 seconds.

When the power comes back on, we Verify the database and if return is FALSE we Repair.

The database gets corrupted after 15 to 24 hours.

Looking at the log, for the first 12 hours there is no Repair going on. Every time the Verify returns True. After that, we start seeing Repair going on. The messages in the log are one or more of the following:

"Page with valid ordinal not found - idPage: 1, iPage: 3151"

"Block page not found - pwszTable: EventLogTable, idPage: 4678"

"Selected page not found - idPage: 4951, iPage: 3935"

After a couple more hours of repairing , the database corrupts completely and our application does not start anymore. The database file is always 20K when it gets corrupted.

Is there any setup to be done for the OS or the SQL to be able to survive this kind of test

Is there anything to do to the database to prevent the corruption

First of all, are you using transactions to submit your changes

If yes, did you check file system to see if it got corrupted Which file system is that Do you have write cache disabled on HDD

Ilya,

Thanks for the reply!

We use the database as follows:

We open a connection using SqlCeConnection.Open()

We prepare a command using SqlCeCommand.Prepare()

We write to database using SqlCeCommand.ExecuteNonQuery()

We keep same connection open all the time while we are writing.

At this time we don't read from database, we just write.

The operating system is XP Embedded. The file system is NTFS.

I am not sure if there is any corruption in the file system but I assume it is not since I just delete the bad database and restart the test and everything goes back to normal. How would I check to see if there is any file system corruption

I will check your suggestion about disabling HDD write cache. I doubt that it is disabled.

Ilya,

I disabled the cache write for the drive.

I will restart test and hopefuly this is the solution.

Thanks again!

Thanks.

We will change the code to use transactions.

Ilya,

We changed the code to use transaction and the database still fails.

Also, the write cache for the hdd is disabled.

This time it failed after 10 hours.

The behaviour is the same as before, the only difference is that now I did not get a lot of pages not found before the program stopped working.

This is the last entry in the database log before it crashed:

Here is the code where we are accessing the database:

#region Database Access

private void InitializeDatabase()

Trace.WriteLine(DateTime.Now.ToLongTimeString() + " EventManager.Constructor Started");

Stopwatch stopwatch = new Stopwatch();

// Determine if database file exists

bool databaseExists = File.Exists(_DatabaseFilePath);

if (_CreateEmptyDatabase || !databaseExists)

// Remove old database file if we are forcing creating of an empty database

if (databaseExists)

File.Delete(_DatabaseFilePath);

// Create an empty database if it does not exist yet

else

Trace.WriteLine(DateTime.Now.ToLongTimeString() + " EventManager.Constructor Completed in " + stopwatch.ElapsedMilliseconds + " msec");

private void VerifyAndRepairDatabase()

Trace.WriteLine(DateTime.Now.ToLongTimeString() + " VerifyAndRepairDatabase Started");

Stopwatch stopwatch = new Stopwatch();

using (SqlCeEngine sqlCeEngine = new SqlCeEngine(_ConnectionString))

LogFile.Log("Event Log Database", "Verified Started");

if (sqlCeEngine.Verify())

Trace.WriteLine(DateTime.Now.ToLongTimeString() + " Database Verify Completed in " + stopwatch.ElapsedMilliseconds + " msec");

LogFile.Log("Event Log Database", "Verified OK");

else

LogFile.Log("Event Log Database", "Verified Failed");

Trace.WriteLine(DateTime.Now.ToLongTimeString() + " Database Corrupted --> Repair in Progress");

sqlCeEngine.Repair(null, RepairOption.DeleteCorruptedRows);

Trace.WriteLine(DateTime.Now.ToLongTimeString() + " Database Verify and Repair Completed in " + stopwatch.ElapsedMilliseconds + " msec");

LogFile.Log("Event Log Database", "Repair Complete");

private void CreateEmptyDatabase()

Trace.WriteLine(DateTime.Now.ToLongTimeString() + " CreateEmptyDatabase Started");

Stopwatch stopwatch = new Stopwatch();

using (SqlCeEngine sqlCeEngine = new SqlCeEngine(_ConnectionString))

Trace.WriteLine(DateTime.Now.ToLongTimeString() + " CreateEmptyDatabase Completed in " + stopwatch.ElapsedMilliseconds + " msec");

private void OpenConnection()

Trace.WriteLine(DateTime.Now.ToLongTimeString() + " OpenConnection Started");

Stopwatch stopwatch = new Stopwatch();

_Connection = new SqlCeConnection(_ConnectionString);

Trace.WriteLine(DateTime.Now.ToLongTimeString() + " OpenConnection Completed in " + stopwatch.ElapsedMilliseconds + " msec");

private void CreateTables()

const string sqlCreateTableCommand =

@"CREATE TABLE " + TableName + " " +

@"(" +

@"[TimeStamp] bigint IDENTITY(1,1) PRIMARY KEY," +

@"[Name] nvarchar(255)," +

@"[DateTime] datetime," +

@"[EventType] integer," +

@"[SourceText] nvarchar(255)," +

@"[MessageText] nvarchar(255)" +

@");";

Trace.WriteLine(DateTime.Now.ToLongTimeString() + " CreateTables Started");

Stopwatch stopwatch = new Stopwatch();

using (SqlCeCommand sqlCommand = new SqlCeCommand(sqlCreateTableCommand, _Connection))

Trace.WriteLine(DateTime.Now.ToLongTimeString() + " CreateTables Completed in " + stopwatch.ElapsedMilliseconds + " msec");

private void PrepareInsertEventCommand()

const string sqlInsertCommand =

@"INSERT INTO " + TableName + @" " +

@"([Name], [DateTime], [EventType], [SourceText], [MessageText]) " +

@"VALUES (@Name, @DateTime, @EventType, @SourceText, @MessageText)";

_InsertEventCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 255);

_InsertEventCommand.Parameters.Add("@DateTime", SqlDbType.DateTime, 8);

_InsertEventCommand.Parameters.Add("@EventType", SqlDbType.Int);

_InsertEventCommand.Parameters.Add("@SourceText", SqlDbType.NVarChar, 255);

_InsertEventCommand.Parameters.Add("@MessageText", SqlDbType.NVarChar, 255);

public void UpdateEventHistory(Event machineEvent)

// Start transaction

SqlCeTransaction transaction = _Connection.BeginTransaction();

try

// Update command parameters and write values to database

catch (Exception exception)

throw exception;

Thanks.

Maybe you should try changing the CommitMode as per http://msdn2.microsoft.com/en-us/library/esdw1h9d.aspx

(default CommitMode is deferred)

// Commit the changes to disk if everything above succeeded;
// Use Deferred mode for optimal performance; the changes will
// be flashed (sic!) to disk within the timespan specified in the
// ConnectionString 'FLUSH INTERVAL' property; ( = 10 seconds)
//
tx.Commit(CommitMode.Deferred);
// Alternatively, you could use:
// tx.Commit(CommitMode.Immediate);

Erik,

I changed the code to use commit immediate and the test still fails after about 12 hours.

In my case the database is completely corrupt, since it has only 20k. I think trying to compact will do nothing.

After reading the article you pointed to, looks like I cannot use this database for this kind of application.

I might try just another test by limiting the size. Right now I keep writing to the database at a rate of about 2Mb per hour.

This means my database is about 24Mb when it becomes corrupted. I will try to copy the file and start over when I reach a certain size.

Any other suggestion other than 'don't reset during a transaction'

Thanks.

What is the behavior of the SQL Express database when you power down during a transaction commit Does SQL Express have the same possible issue of getting the database corrupt as the Compact does

That likely indicates file system corruption- your file is just plain gone. Consider running chkdsk.exe before running database fix. That might restore the file to pre commit state.