Repairing the Umbraco SqlCe Database

Posted on December 8, 2016 in umbraco

As reported in this thread our Our, or this issue in the issue tracker, it can happen that some Umbraco operations fail to execute when running on top of a SqlCe database.

It usually is some basic operation that should execute flawlessly, such as deleting a content item or a content type, but instead produces weird errors such as "The key to be deleted is already deleted from an index by another concurrent session".

After much Googling and testing, it turns out that this error indicates a database corruption of some sort, that can in some cases be fixed by repairing the database. Tools such as SqlCeToolbox can do it—but there is a catch: if the tool is built against SqlCe 4.0.0.0 (dll version 4.0.8876.1 aka "4.0 SP1"), it might not be able to detect the corruption and repair it.

This is because Umbraco is built against SqlCe 4.0.0.1 (dll version 4.0.8902.1 aka "latest hotfix"). What we really want is to repair the database, using that same version. Why not let Umbraco do it, then? This is exactly what the following bit of code does, when dropped into ~/App_Code: verify (and repair) the database when Umbraco starts.

You probably do not want to leave the file there, but only execute it once. The code writes to Umbraco's log so you can see whether your database was verified and repaired.

A few things to note:

• No idea about why the database ended up being corrupted. But SqlCe definitively has flaws, which will probably never be fixed as it has been deprecated by Microsoft.
• This could be part of Umbraco Core, as a new HealthCheck that would only appear when using a SqlCe database. Up for grab!

And now... code:

using System;
using System.Data;
using System.Data.SqlServerCe;
using Umbraco.Core;
using Umbraco.Core.Logging;

namespace UmbracoVerifyCe
{
    public class VerifyCe : ApplicationEventHandler
    {
        protected override bool ExecuteWhenApplicationNotConfigured
        {
            get { return true; }
        }
        
        protected override bool ExecuteWhenDatabaseNotConfigured
        {
            get { return true; }
        }
    
        protected override void ApplicationStarted(UmbracoApplicationBase umbracoApplication, ApplicationContext applicationContext)
        {
            LogHelper.Info<VerifyCe>("Verifying SqlCE!");
            
            const string cstr = @"Data Source=|DataDirectory|\Umbraco.sdf;Flush Interval=1;";
            
            var engine = new SqlCeEngine(cstr);
            try
            {
                var verified = engine.Verify(VerifyOption.Enhanced);
                LogHelper.Info<VerifyCe>("VERIFIED: " + verified);

                if (!verified)
                {
                    LogHelper.Info<VerifyCe>("REPAIRING");
                    engine.Repair(cstr, RepairOption.RecoverAllOrFail);
                    engine.Compact(cstr);

                    verified = engine.Verify(VerifyOption.Enhanced);
                    LogHelper.Info<VerifyCe>("VERIFIED: " + verified);
                }
            }
            finally
            {
                engine.Dispose();
            }
        }
    }
}

Enjoy!