Posts

Showing posts from 2019

EPiServer CMS 11 Useful SQL Queries - 2

Here is a set of few queries that we have been using in different investigations


Get usages of EPiServer contents including pages and blocksCheck Table sizeNo contents have been added for following content typesLooking into Activity LogsUnmapped 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 int…

EPiServer CMS 11 Useful SQL Queries - 1

Here is a set of few queries that we have been using in different investigations
Check How Big is your DatabaseGet Data for Each Property and from Each Content TypeComplete Tree Structure of your web siteCheck EPiServer DB Version
Check How Big is Database
SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partiti…