Cleaning versions in the umbraco db for given nodes via SQL

In this thread I want to explain how to clean the umbraco database from unnecessary versions to reduce the database size. Again I got some mails pointing me to help them to clean the database from millions of versions some nodes have. In one of the last mails Jennifer mentioned that she has 4 nodes which have each more than 900.000. This results in a size for the cmsPropertyData table of more than 1.7 GB.

Before we go on I have to mention again that 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. Also please read my post Understanding the umbraco database first to get a glue of what you are doing.

If you only have a few nodes which have millions of versions you can clean them easily via SQL, far from it it is the easiest way. In the next paragraphs I will show how you can do it by yourself.

Identifying the nodes

First of all identify the nodes which have the various versions and their ids. As in my first post described it can be done with the following SQL:

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

Just mark the ids down.

Get the newest and the published version

Each time umbraco saves or publishes a node a new version is created in the database. But for each published version there is always a newer version in the database. But why? Umbraco is by concept working with a new version (a copy of the published version, or a copy of the last saved version) and not the one which is published. So it prevents the user to break the published version. So if you publish a node the actual version you are working on is saved as the published one, the previously published version is marked as not published, a new version is created and send back to the umbraco user to let him work with this. Each published node has a published and a newest row in cmsDocument:

For each id (node) you now have to identify which version is the newest and which version is the published one. I added some variables via DECLARE for typing horror:

DECLARE @id int
SET @id = 3243

DECLARE @newest UNIQUEIDENTIFIER
SET @newest = (SELECT versionId FROM cmsDocument WHERE nodeId = @id AND newest = 1)

DECLARE @published UNIQUEIDENTIFIER
SET @published = (SELECT versionId FROM cmsDocument WHERE nodeId = @id AND published = 1)

Optional: Delete the indexes

The cmsPropertyData table has four indexes for better reading performance. For deleting it slows down and let the SQL Server log file grow heavily. So if you have the possibility you can delete the indexes first like this:

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[cmsPropertyData]') AND name = N'IX_cmsPropertyData')
DROP INDEX [IX_cmsPropertyData] ON [dbo].[cmsPropertyData] WITH ( ONLINE = OFF )

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[cmsPropertyData]') AND name = N'IX_cmsPropertyData_1')
DROP INDEX [IX_cmsPropertyData_1] ON [dbo].[cmsPropertyData] WITH ( ONLINE = OFF )

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[cmsPropertyData]') AND name = N'IX_cmsPropertyData_2')
DROP INDEX [IX_cmsPropertyData_2] ON [dbo].[cmsPropertyData] WITH ( ONLINE = OFF )

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[cmsPropertyData]') AND name = N'IX_cmsPropertyData_3')
DROP INDEX [IX_cmsPropertyData_3] ON [dbo].[cmsPropertyData] WITH ( ONLINE = OFF )

You can create these entry's by a rightclick on the index in SQL Server Management Studio.

Delete the unnecessary versions

Now you can delete the not needed versions via

DELETE FROM cmsPropertyData WHERE contentNodeId = @id AND versionId NOT IN (@newest, @published)
DELETE FROM cmsContentVersion WHERE ContentId = @id AND VersionId NOT IN (@newest, @published)
DELETE FROM cmsDocument WHERE nodeId = @id AND versionId NOT IN (@newest, @published)

Be aware that these scripts can last a very long time thus you deleted the indexes (without deleting the indexes it will last hours ;-))

Recreate indexes

After deleting all unneeded versions you can now recreate the indexes:

CREATE NONCLUSTERED INDEX [IX_cmsPropertyData] ON [dbo].[cmsPropertyData]
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_cmsPropertyData_1] ON [dbo].[cmsPropertyData]
(
    [contentNodeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_cmsPropertyData_2] ON [dbo].[cmsPropertyData]
(
    [versionId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_cmsPropertyData_3] ON [dbo].[cmsPropertyData]
(
    [propertytypeid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

After completing the cleaning you now can reorganize or shrink your database files.

Conclusion

The complete SQL script can be downloaded here.

I hope I could show how various versions easily can be deleted via SQL if you have a growing database like in this umbraco forum post described. As mentioned before please be careful with your database and make a backup first. If you have any suggestions just write a comment or get in contact with me via mail (th |at| thoehler |dot| com).