Repairing the Umbraco SqlCe Database

Thursday, December 8, 2016 11:33 AM 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:

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!

comments powered by Disqus