Analyzing SQL Server database size

Here's a query that will give you the biggest tables by row count.

SELECT so.name AS [TableName], MAX(si.rows) AS [RowCount]
FROM sysobjects so, sysindexes si
WHERE so.xtype = 'U' AND si.id = OBJECT_ID(so.name)
GROUP BY so.name
ORDER BY 2 DESC

If you already suspect a table you can find out how much disk space it uses by running the following procedure (where tblWorkContentProperty could be any table name).

EXEC sp_spaceused 'tblWorkContentProperty'

All tables in one neatly sorted result set is surely more useful. This is what the following query does using a table variable.

SET NOCOUNT ON
DECLARE @SpaceUsedTotals TABLE
(
  table_name sysname,
  row_count INT,
  reserved_size VARCHAR(50),
  data_size VARCHAR(50),
  index_size VARCHAR(50),
  unused_size VARCHAR(50)
)
INSERT @SpaceUsedTotals
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name AS TableName, a.row_count AS [RowCount],
  COUNT(*) AS ColCount, a.data_size AS DataSize
FROM @SpaceUsedTotals a
INNER JOIN information_schema.COLUMNS b ON a.table_name
  COLLATE database_default = b.table_name COLLATE database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC

If you get info that suggests massive amounts of page versions you can run something like the Remove old versions job in Episerver CMS 6 from Karoline Klever's blog and then shrink the database if you are low on space.

You should also set uiMaxVersions in siteSettings in EPiServer.config to something else than 0 to block too many versions from being created again. I usually set 9 or 14 which makes the total including the published page 10 or 15. Not doing this can also be bad for the performance of your site.

If you see huge numbers for your own tables or Episerver tables that you know, just DELETE rows from them before shrinking.

If it's the database log that's gone massive you can clear it completely by running these commands. I would only suggest this for development environments since you most likely have the log there in the first place for a reason. You first need to set the recovery mode of the database to simple.

DBCC SHRINKFILE("logicalname_log", 1)
BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE("logicalname_log", 1)

Good luck!