Insight into SQL Server Compact database corruptions
Database corruptions are often the most painful bugs to repro and debug. A good percentage of the SQL Server Compact bugs reported are in this category. However it so turns out that in majority of the cases Compact is not the culprit. Surprised!! Let us see why.
How SQL Server Compact detects a file corruption?
Compact database comprises of a series of pages, each 4K in size. When a page is written to disk the following sequence of steps is executed.
1. Compute the checksum on the page data and store it as part of the page.
2. Encrypt the page.
3. Write to disk at a given file location or offset.
4. Update system pages/run time cache with the page offset.
When a page is read from disk the following sequence of steps is executed.
1. Read from the disk.
2. Decrypt the page.
3. Re-compute the checksum and compare it with the expected value. (If it fails page is not fully baked. Meaning file write failed)
4. Check if this is the same page that we are expecting as per the system pages/ run time cache. (If this fails we lost a complete page write)
If (3) or (4) of the above read steps fail, the db is marked as corrupt and any further db activity is suspended till it is repaired successfully.
Notes:
· Verify reports step (3) errors as “Bad Checksum - idPage: %d, tyPage: %d, iPage: %d”.
· Verify reports step (4) errors as “Page with valid ordinal not found - idPage: %d, iPage: %d”
What could have triggered the corruption?
1. A simple device on/off or suspend/resume can result in half-baked file. (No reason to panic as we handle this as described in the next section.)
2. Compact relies on underlying FAT/TFAT file system to manage the database file. Any bugs in these components can lead to Compact file corruption. Compact stresses file system in many different ways as it tries to keep the file size as small as possible. As it employs shadow paging the writes and reads are quite random. (Note: 6 months back a bug has been identified in FAT/TFAT system that results in a Compact file losing some of the clusters during shrink operation.)
3. If SD card is being used for db file storage, it’s being equally stressed as the above.
4. More importantly SD cards bring in additional complexities during Suspend/Resume cycles. Here is a good blog link: https://blogs.msdn.com/windowsmobile/archive/2007/01/12/everything-you-want-to-know-about-sd.aspx
Am I suggesting SQL Server Compact bugs can’t result in any kind of corruption?
Of course Compact code bugs can result in corruption. A lot of them have been found internally before v3.0 release and also reported by customers thereafter. They are more logical in nature like index constraint is compromised or a table page says there are 1000 rows when there are only 55. We did a very good job in fixing these and they are hard to find now. However Compact bugs don’t result in physical corruptions like “checksum errors” or “page missing” errors.
What does Compact do to prevent corruptions?
We believe most of the corruptions should be due to device suspend/resume or on/off cycles. For ex: Compact is trying to write to a file and the user simply boots his device. Now file is in half-baked state. Or let’s say process gets killed before it can complete the flush cycle. How do we avoid this?
Well, we use shadow paging to overcome this situation. We can loosely categorize the db pages into user data pages, system pages and header page. As the name suggests user data pages contain all the user data. Header pages and system pages help us locate a specific user page on the disk. When it’s time to flush the changes to disk, we write shadows of the user data pages first. Then the shadows of the updated system pages are written. Finally the header page is written. The old pages are retained till the header page is updated. This ensures that we have a transactional write to the disk. Either the change is completely present or not.
Conclusion: Why do we still see corruptions?
Clearly the above is not enough. Some of the interesting cases are
· What if any of the underlying systems ‘SD card software’ or FAT/TFAT has a bug?
· Suspend/resume cycle can result in file buffer loss if SD card is used. So the changes supposed to be written to disk could be lost.
The intent is not to blame other components. However in the last 3 years, at least 90% of the corruption bugs reported are due to bad checksum. It means disk write is bad for some reason (which is not owned by Compact). The fault is somewhere in the bottom stack and it has to be fixed. Our options are very limited without a repro but we are trying hard to improve our debugging infrastructure. We are hoping that with this knowledge customers will be able to understand the corruptions better and come up with good practices that would vastly reduce these corruptions.
Thanks
Raja
Comments
Anonymous
July 07, 2008
Hello! In case of data corruption, what tools are available for the User to use to repair their SSC-based application database? I have set up our facility with a program which uses SSC as its database engine, but there seems to be no Management console to maintain the database with! Thanks in advance, Ray Collazo El Camino Ranch rcollazo@elcaminoranch.comAnonymous
July 17, 2008
The comment has been removedAnonymous
August 07, 2008
[Pardon us for late response] Hey Ray Collazo, SQL Server Compact is manageable using SQL Server Management Studio. For SQL CE 3.1 - Use SQL Server 2005 Management Studio For SQL CE 3.5 - Use SQL Server 2008 Management Studio you could also use Visual Studio 2005, 2008 namely to operate on the databases. Now coming to repair the databases. As explained in this article, all database connections are suspeneded once the database is marked as corrupted so these tools do not help. you need to use SqlCeEngine.Repair API to repair a database. Thanks, Laxmi