none
SqlCeEngine - Repair / Verify - General database best practise

    General discussion

  • Guys

    There doesn't seem to be any good best practise documented on when to call the Repair/ Verify methods in the SqlCeEngine class. I understand well what the methods do however I'm unsure when they should be called.

    Should we call the Verify method at start up of the application every time. If the application could be running for a long period of time would it be useful to call Verify at regular intervals. Should we catch a specific type of exception and call Repair.

    I did follow up a similar discussion with some of the sql server ce developers but didn't really get any definitive answers. e.g. there is an error code

    #define SSCE_M_DATABASECORRUPTED                25017           // The database file may be corrupted. Run the repair utility to check the database file. [,,,Database name,,]

    Therefore should we attempt to catch this type of excepiton through out the application and call Repair automatically in the exception handler?

    Appreciate thoughts from microsoft as well as others who are using Sql Server Ce and calling Repair / Verify.
    Thursday, February 5, 2009 3:25 PM

All replies

  • Hi Matt,
    Interesting discussion topic.
    I would say that similar to all other things in software, the answers would be contextual. However, I think there can certainly be some best practices around this.

    Judging by your questions, I think you understand the purpose and behavior of these APIs. So, I will continue after that.

    1. Calling Verify at Application start(and maybe even Application Exit) is a good idea: This ensures that your application is going to work on a valid database file and prevents you from unexpected consequences. (Eg: Updating the backend database fails(due to db corruption) when the input has already been used to fill other program structures or GUI)

    Verify throws an exception if there is a failure and you can catch the exception to do the processing.

    Just a warning: Verify and Compact require exclusive access to the physical file during thier operation. (The reason should be obvious). Hence if you have open connections or other applications connecting to the same database file, then you will get an exception.

    So, call verify at the beginning of the app and if you get exception:
    1. During development, ensure that it is not because of wrong connection string, Open connections etc.
    2. In production, you can call Repair when you catch the exception.

    Another Side Note: Remember that any database recovery cannot gaurantee a hundred percent data recovery in all the cases. So, you have a very sensitive high end app, you might want to have some mechanisms in place for these rare cases. For the 1% scenarios where you simply cannot live with lost data, try Hourly backups, Back ups at start of app etc....  For most of the normal cases, you should be fine with the above verify-repair combo.

    2. Catching Database corrupted error throughout the app or calling verify/repair every time:
    It does not sound good even by the looks of it and I am sure you will not be happy with the code that comes out of this tedious process :)
    I would rather suggest that you let every function handle contextual errors ( like PK violation etc) and let them throw such unexpected errors to the main function. You can have a global exception handler here to catch the Database corrupted error and call Repair.
    I would be really surprised if you are getting this error frequently in your application and would rather suggest that you do some other actions like checking the storage card or file system driver.

    That's my perspective. I would be interested in knowing how applications are actually using the same.
    Friday, February 6, 2009 7:25 AM
  •  Thanks Rohit,

    Verify

    Are you sure you should catch an exception on a Verify call? The documentation http://msdn.microsoft.com/en-us/library/a0a5czch(VS.80).aspx doesn't mention this and only indicates a return value of true / false.  I guess it doesn't make much sense to call on application exit as well as start up given the application will always have to start after exiting. Having exclusive access shouldn't be an issue here because on start up the application can wait until the operation completes before continuing.

    I assume you meant "Verify and Repair" and not "Verify and Compact"?

    Repair

    I think it makes sense to take a copy of the existing sdf file before calling Repair e.g. MyDatabase.sdf -> MyDatabase050209-01.sdf. So the old copy can be retrieved if required or if any loss of data occurs.

    In relation to exception handling and calling Repair I'd be a little concerned with this only because you shouldnt really execute any code in a catch block which can also throw an exception. However from the documentation it doesn't state any exceptions can be thrown ( http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlceengine.repair(VS.80).aspx ) , is this is indeed the case or are there any specific exception types we might expect here?

    Our existing application has an unhandled exception filter installed which could detect this exception with the execption code I mentioned i.e.  25017 so we could filter it out here and call Repair.

    Also regards the RepairOption value you can pass in what value would you suggest to use it?

    Friday, February 6, 2009 2:02 PM
  • Any ideas?

    Thursday, February 12, 2009 2:19 PM