Understanding the umbraco database

In the last weeks I got many mails asking for help regarding a growing umbraco database. Two months ago I helped a friend cleaning his database which was grown up to 8 GB. But be aware: working in the database is not best practice. We don't know exactly the internal relations how umbraco handles the entries in the database. We don't know what effects a direct change in the database has.

Regarding this I have to say: this post is a "Works on my machine" post. Please be sure what you do and please, please make a backup of your database first.

�

umbracoNode

Ok, one of the main things you have to know regarding the umbraco database is the umbracoNode table. This table is the main table where all things in umbraco, regardless of which type or meaning, are stored. This means that there are content nodes, members, document types, property types, and all other things in this table. How does umbraco now identify what kind of type this row is? Is it a content node or a member or a document type? The answer is stored in the column nodeObjectType. Each type has its own GUID. The GUID is defined in the code of Umbraco. For example: a document has the GUID "c66ba18e-eaf3-4cff-8a22-41b16d66a972". It is defined in the umbraco.cms.businesslogic.web.Document class:

namespace umbraco.cms.businesslogic.web
{
    /// <summary>
    /// Document represents a webpage,
    /// type (umbraco.cms.businesslogic.web.DocumentType)
    ///
    /// Pubished Documents are exposed to the runtime/the public website in a cached xml document.
    /// </summary>
    public class Document : Content
    {
        public static Guid _objectType = new Guid("c66ba18e-eaf3-4cff-8a22-41b16d66a972");
        ...

Documents and versions

After knowing what the umbracoNode table is we can try to identify which tables are related to documents and versions. We have to look at the following tables:

• cmsContent:
  The cmsContent table contains only the nodeids from all content documents (this means all ids from the umbracoNode where the nodeobjectType is the document guid).
• cmsContentVersion
  The cmsContentVersion contains all definitions from all versions a node (also members etc.) has. Each version is stored with the create date and an own guid for identifying.
• cmsContentXml
  The cmsContentXml stores the xml representation of each content node from the published version.
• cmsDocument
  The cmsDocument stores some information for each node per version. here is defined if this version is published or if it is the newest.
• cmsPropertyData
  The cmsPropertyData now stores for each node per version and per created "Generic Property".

You can see all these tables and their database relations here:

Identifying with which nodes you have problems

Coming back to the growing databases. You can now identify which nodes have massive publishing issues by the number of versions the nodes have. For example you can run the following SQL:

SELECT nodeId, COUNT(*) Number 
FROM cmsDocument
GROUP BY nodeId
HAVING COUNT(*) > 2
ORDER BY Number DESC 

This SQL gives you all nodeIds which have more than two versions (a published node has at minimum two versions). Check the nodes with massive numbers, unpublish it or delete it manually and the issue should stop.

I hope you got a glue on how umbraco acts in the database. If you have massive number of versions you now can identify the related nodes. In the next post I will explain how to delete the old versions manually or by my ClientTools.

Thomas