The performance of our Umbraco web site has become horrible again. It seems the log files and versions get out of hand slowing performance down over time. If I use the backoffice utilities I can regain some performance. The biggest problem is, it takes me forever as they often timeout and worst of all, can leave behind orphaned records if they don't complete.
Does anyone know where all the various peices are in the database so I don't have to dig for them? What SQL queries can I run on the backend, perhaps on a schedule, to periodically clean out multiple versions or trim the logs?
Options then are custom SQL scripts - or how running that package with an increased value for the ASP.NET timeout (or even in vs.net debug mode so that it'll never timeout) ?
If you want to try the SQL route, then this script may help as a starting point:
-- Create a temporary table for all documents which are published and not in the recycle bin
CREATE TABLE #Nodes (id int)
-- Delete all rows if the table exists before
TRUNCATE TABLE #Nodes
-- Insert all nodeIds from all documents which are published and not in the recycle bin
INSERT INTO #Nodes
SELECT N.id
FROM umbracoNode N
INNER JOIN cmsDocument D ON N.ID = D.NodeId
WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972'
AND [path] NOT LIKE '%-20%'
AND D.Published = 1
-- Create a temporary table for all versionId's to delete
CREATE TABLE #Versions (id UniqueIdentifier)
-- Delete all rows if it exists before
TRUNCATE TABLE #Versions
-- Insert all versionId's from all nodeIds in the #Nodes table
-- and where published is set to false and newest is set to false
INSERT INTO #Versions
SELECT versionId
FROM cmsDocument
WHERE nodeId IN (SELECT id FROM #Nodes)
AND published = 0 AND newest = 0
-- DELETE all versions from cmsPropertyData, cmsContentVersion, cmsDocument
-- from the nodes which are published and which are not in the recycle bin
-- and which are not published and which are not the newest
DELETE FROM cmsPropertyData WHERE VersionId IN (SELECT id FROM #Versions)
DELETE FROM cmsContentVersion WHERE VersionId IN (SELECT id FROM #Versions)
DELETE FROM cmsDocument WHERE VersionId IN (SELECT id FROM #Versions)
Looking for Umbraco cleanup scripts
The performance of our Umbraco web site has become horrible again. It seems the log files and versions get out of hand slowing performance down over time. If I use the backoffice utilities I can regain some performance. The biggest problem is, it takes me forever as they often timeout and worst of all, can leave behind orphaned records if they don't complete.
Does anyone know where all the various peices are in the database so I don't have to dig for them? What SQL queries can I run on the backend, perhaps on a schedule, to periodically clean out multiple versions or trim the logs?
Hi Connie, have tried the FALM Housekeeping package ?
I have. Pretty worthless if you are trying to clean up a lot of old data. It timesout and then leaves orphaned records behind creating more of a mess.
Options then are custom SQL scripts - or how running that package with an increased value for the ASP.NET timeout (or even in vs.net debug mode so that it'll never timeout) ?
There's also the UnVersion package.
If you want to try the SQL route, then this script may help as a starting point:
-- Create a temporary table for all documents which are published and not in the recycle bin CREATE TABLE #Nodes (id int) -- Delete all rows if the table exists before TRUNCATE TABLE #Nodes -- Insert all nodeIds from all documents which are published and not in the recycle bin INSERT INTO #Nodes SELECT N.id FROM umbracoNode N INNER JOIN cmsDocument D ON N.ID = D.NodeId WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972' AND [path] NOT LIKE '%-20%' AND D.Published = 1 -- Create a temporary table for all versionId's to delete CREATE TABLE #Versions (id UniqueIdentifier) -- Delete all rows if it exists before TRUNCATE TABLE #Versions -- Insert all versionId's from all nodeIds in the #Nodes table -- and where published is set to false and newest is set to false INSERT INTO #Versions SELECT versionId FROM cmsDocument WHERE nodeId IN (SELECT id FROM #Nodes) AND published = 0 AND newest = 0 -- DELETE all versions from cmsPropertyData, cmsContentVersion, cmsDocument -- from the nodes which are published and which are not in the recycle bin -- and which are not published and which are not the newest DELETE FROM cmsPropertyData WHERE VersionId IN (SELECT id FROM #Versions) DELETE FROM cmsContentVersion WHERE VersionId IN (SELECT id FROM #Versions) DELETE FROM cmsDocument WHERE VersionId IN (SELECT id FROM #Versions)HTH,
Hendy
is working on a reply...