Here is a set of few queries that we have been using in different investigations
- Get usages of EPiServer contents including pages and blocks
- Check Table size
- No contents have been added for following content types
- Looking into Activity Logs
- Unmapped Property List
Get usages of EPiServer contents including pages and blocks
SELECT
tct.Name,
tct.ModelType,
COUNT(tc.pkID) AS PageCount
FROM
tblContent AS tc RIGHT OUTER JOIN
tblContentType AS tct ON tc.fkContentTypeID = tct.pkID
Where tct.ModelType is not null and tct.ModelType not like ‘EPiServer.%’
GROUP BY
tct.Name, tct.ModelType
ORDER BY
PageCount desc
Check table size
EXEC sp_spaceused ‘tblBigTable’
No contents have been added for following content types
SELECT
tct.Name,
tct.ModelType
FROM
tblContent AS tc RIGHT OUTER JOIN
tblContentType AS tct ON tc.fkContentTypeID = tct.pkID
Where tct.ModelType is not null and tct.ModelType not like ‘EPiServer.%’
GROUP BY
tct.Name, tct.ModelType
Having COUNT(tc.pkID) = 0
Looking into Activity Logs
exec netActvitiyLogList @from=’2018-02-01′, @to=’2019-02-14′, @maxRows=10
Unmapped Property List
tblContentType.ModelType
FROM tblContentProperty INNER JOIN
tblPageDefinition ON tblContentProperty.fkPropertyDefinitionID = tblPageDefinition.pkID INNER JOIN
tblContent ON tblContentProperty.fkContentID = tblContent.pkID INNER JOIN
tblContentType ON tblContent.fkContentTypeID = tblContentType.pkID
WHERE (tblContentProperty.LinkGuid IS NOT NULL) AND (tblContentProperty.ContentLink IS NULL)