Resolve SharePoint Document Locked for Editing Issue

This morning, I got a support request from the client I'm consulting with, basically he's having a "SharePoint Document Locked for Editing" issue while working with SharePoint documents. Most of the time, it's a valid warning message, which notifies that someone else might edit the document during the same period of time.

Some other time, it turns out that there is no current user having this document open for editing, and I just couldn't help wondering how this could be possible?

Under the hood, this issue could be caused by the following scenarios:

1. The Microsoft Office products crash while you were working on that particular document
2. PC crashes while the document is open
3. Loss of network connection (network issue) while document is open
4. And more

Well, there are a few quick fixes out there to work around this issue by removing the local copy of the file under the local user account CacheFolder. However, the major downside of this approach is to require the access to the user PC, in order to remove the cached copy from there.

Therefore, what if that particular user doesn't show up for work that day, or we couldn't get hold of the user via the phone/email?

The solution hereby is to build a small program to unlock the document/file against SharePoint Content Database. (I'm fully aware that direct updating SharePoint Content database is not encouraged by Microsoft SharePoint Best Practice Guide), however, if you know how AllDocs table works and make sure that your query will still keep the referential integrity of the data, this approach might work perfectly for you in this case.

The type of the application itself could vary (Windows Form/console/web applications) based upon your speciality and strength, the magic here is the code snippet below, by which it updates the values from CheckoutExpires column in order to drop the lock of the file.

private void UpdateItemCheckoutExpiration(SPListItem item)
        {
    SqlConnection contentDatabaseConnection = null;
    try
    {
        contentDatabaseConnection = 
          new SqlConnection(item.Web.Site.ContentDatabase.DatabaseConnectionString);
        contentDatabaseConnection.Open();

        string updateCommandText = string.Format(
            "UPDATE dbo.AllDocs SET CheckoutExpires " + 
            "= '{0:yyyy-MM-dd HH:mm:ss:fff}' WHERE Id = '{1}'",
            DateTime.Now.ToUniversalTime(), item.UniqueId);

        SqlCommand updateCommand = 
          new SqlCommand(updateCommandText, contentDatabaseConnection);
        SqlDataAdapter contentDataAdapter = new SqlDataAdapter();
        contentDataAdapter.UpdateCommand = updateCommand;
        contentDataAdapter.UpdateCommand.ExecuteNonQuery();
        contentDatabaseConnection.Close();
    }
    catch (Exception)
    {
        // handle exception here
    }
    finally
    {
        if (contentDatabaseConnection != null && 
            contentDatabaseConnection.State != ConnectionState.Closed)
            contentDatabaseConnection.Close();
    }
}

Once the query has been executed against the SharePoint database, the file itself will be released instantly. Therefore, by having this awesome software, we won't worry about some naughty users opening the documents without checking it out and going for lunch, then there's no way to force it free if you can't get hold of their PCs.

Happy SharePointing!