Display what is filling up SQL Express Database on vCenter

Run this in SQL Management Studio to see what is taking up space on vCenter SQL Express Database:

 

SELECT [Table Name],
(SELECT rows FROM sysindexes s WHERE s.indid < 2 AND s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)] FROM ( SELECT QUOTENAME(USER_NAME(o.uid)) + ‘.’ + QUOTENAME(OBJECT_NAME(i.id)) AS [Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = ‘E’)) / 1024.)/1024.)) AS [Total space used (MB)] FROM sysindexes i (NOLOCK) INNER JOIN sysobjects o (NOLOCK) ON i.id = o.id AND ((o.type IN (‘U’, ‘S’)) OR o.type = ‘U’) AND (OBJECTPROPERTY(i.id, ‘IsMSShipped’) = 0) WHERE indid IN (0, 1, 255) GROUP BY QUOTENAME(USER_NAME(o.uid)) + ‘.’ + QUOTENAME(OBJECT_NAME(i.id))

) as a
ORDER BY [Total space used (MB)] DESC

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s