Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm looking for a cause of a really strange case of SQL Server Compact db corruption. The case is that in a .NET Compact Framework app that we are running every once in a while we get into a situation where the following statement returns no rows

select * from order_item where id = '043740d1-c6d0-4fd4-ae84-fdb9218f8b81'

while

select * from order_item where id LIKE '043740d1-c6d0-4fd4-ae84-fdb9218f8b81'

returns one. Actually, when you select all rows from the table you can see that this row indeed exists in the db. The id column is uniqueidentifier, so it's not related to casing etc.

In any case, when I run the sdf file through Repair in SSMS, the first query starts returning the correct row.

I can only guess that the problem is related to some sort of index corruption - maybe it gets out of sync with the actual data store. Neither the app nor SSMS gives any sign of DB corruption - no exceptions, no messageboxes whatsoever.

Anyway, I can't really get to the root cause of this. I think it might have to do with modifying the db from different threads (at some points we may have 2 threads working on the db), but I thought this shouldn't be an issue.

Any idea what might cause this sort of issue?

UPDATE: As @Catcall mentioned dropping the index fixed the issue, so it seems it's related to an out-of synch index. I think it's likely happening when the row is deleted - the index gets updated but the table row isn't for some reason.

share|improve this question

migrated from stackoverflow.com Oct 31 '12 at 20:56

This question came from our site for professional and enthusiast programmers.

    
What is the data type on the order_item column? –  Gromer Oct 31 '12 at 18:55
2  
You could isolate index issues by dropping the index. –  Mike Sherrill 'Cat Recall' Oct 31 '12 at 21:20
    
Does the database come from a desktop app/server, then it is vital that you use a recent version of SQL Server Compact on the device –  ErikEJ Nov 3 '12 at 18:12
    
@Gromer its uniqueidentifier –  Michał Drozdowicz Nov 5 '12 at 11:57
    
@Catcall thanks. That actually fixed the issue, so the problem is in fact caused by invalid index. I'll update the question. –  Michał Drozdowicz Nov 5 '12 at 12:01
show 2 more comments

1 Answer 1

I can only guess that the problem is related to some sort of index corruption - maybe it gets out of sync with the actual data store.

You could isolate index issues by just dropping the index. That will slow down some or all of the queries that access that table, but if the problem goes away, you know for certain that the index was the source of the problem.

But even if the index is the source of your problem, it's not the root cause of your problem. To my way of thinking, the root cause is quite likely to be in Microsoft's code. An index that doesn't match the data is one of those things the database shouldn't allow. And if it happens, you should see big, waving red flags.

You can verify and and try to repair a SQL Server CE database in code. I'm not sure whether this exercises the same code you'd use by trying to repair through SSMS. (I'd hope so, but I know that hope doesn't scale well.)

How to: Verify and Repair a SQL Server Compact Edition Database (Programmatically)

I think it might have to do with modifying the db from different threads (at some points we may have 2 threads working on the db), but I thought this shouldn't be an issue.

Documentation for .NET 3 SqlCEConnection class.

Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.

I think you're safe if each thread uses a separate connection. I could be wrong.

share|improve this answer
    
That works, but it's not an acceptable solution, especially since the column is the PK of the table. –  Michał Drozdowicz Nov 6 '12 at 7:59
    
@MichałDrozdowicz: Updated answer. –  Mike Sherrill 'Cat Recall' Nov 6 '12 at 11:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.