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!
|||Hold on, not just yet… You have to use transactions for all changes to the database to engage ACID and it looks like you’re not using them. Please see this on how to use transactions.
|||
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:
Source : D:\MC\7654321\DomainModel\EventLog.sdf
Destination : n/a
Verifying - verMajor: 3, verMinor: 0, verBuild: 5300, verPoint: 0
Time - 2007-22-03 22:38:19.821
Selected page not found - idPage: 1, iPage: 3007
Source : D:\MC\7654321\DomainModel\EventLog.sdf
Destination : C:\DOCUME~1\husky\LOCALS~1\Temp\sql1.tmp
Repairing - verMajor: 3, verMinor: 0, verBuild: 5300, verPoint: 0
Time - 2007-22-03 22:38:20.190
Selected page not found - idPage: 1, iPage: 3007
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();
stopwatch.Start();
// 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
CreateEmptyDatabase();
OpenConnection();
CreateTables();
}
else
{
VerifyAndRepairDatabase();
OpenConnection();
}
PrepareInsertEventCommand();
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();
stopwatch.Start();
using (SqlCeEngine sqlCeEngine = new SqlCeEngine(_ConnectionString))
{
LogFile.Log("Event Log Database", "Verified Started");
if (sqlCeEngine.Verify())
{
stopwatch.Stop();
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);
stopwatch.Stop();
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();
stopwatch.Start();
using (SqlCeEngine sqlCeEngine = new SqlCeEngine(_ConnectionString))
{
sqlCeEngine.CreateDatabase();
}
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();
stopwatch.Start();
_Connection = new SqlCeConnection(_ConnectionString);
_Connection.Open();
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();
stopwatch.Start();
using (SqlCeCommand sqlCommand = new SqlCeCommand(sqlCreateTableCommand, _Connection))
{
sqlCommand.ExecuteNonQuery();
}
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 = _Connection.CreateCommand();
_InsertEventCommand.CommandText = sqlInsertCommand;
_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);
_InsertEventCommand.Prepare();
}
public void UpdateEventHistory(Event machineEvent)
{
// Start transaction
SqlCeTransaction transaction = _Connection.BeginTransaction();
_InsertEventCommand.Transaction = transaction;
try
{
// Update command parameters and write values to database
_InsertEventCommand.Parameters[0].Value = machineEvent.Name;
_InsertEventCommand.Parameters[1].Value = machineEvent.TimeStamp;
_InsertEventCommand.Parameters[2].Value = machineEvent.EventType;
_InsertEventCommand.Parameters[3].Value = machineEvent.SourceText;
_InsertEventCommand.Parameters[4].Value = machineEvent.MessageText;
_InsertEventCommand.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception exception)
{
transaction.Rollback();
_Connection.Close();
throw exception;
}
}
#endregion
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);
Have you looked at this: http://support.microsoft.com/kb/296076/en-us ?
|||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.
|||Hi, Ilya;
I have database corruption problem as well.
I found a very strange issue: my application will write some log to a log file "log.txt", but I found a corrupted .sdf database file has my log information with plain text !!!
How can I tell there is file system corruption? How can this happen? How can I find chkdsk.exe?
Cheers.
|||Is this corruption problem still valid with 3.5 version ?Arnaud.
|||Sqlce is a standalone file . so if the file was corrupt.
It looks like no good way to repaire it .
No comments:
Post a Comment