Aron's Tech Blog

I was trying to explain to my boss that the SQL database we’re using for the intranet was somewhere in the 400 MB range when he asked ‘What’s taking up all the space?’  I was a little taken aback, I mean databases collect data and that takes up space right?  Well it got me thinking what the hell is taking up all the space.  I found this great query that tells all:)

</pre>
SELECT
 t.NAME AS TableName,
 i.name AS indexName,
 SUM(p.rows) AS RowCounts,
 SUM(a.total_pages) AS TotalPages,
 SUM(a.used_pages) AS UsedPages,
 SUM(a.data_pages) AS DataPages,
 (SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB,
 (SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB,
 (SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB
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.partition_id = a.container_id
WHERE
 t.NAME NOT LIKE 'dt%' AND
 i.OBJECT_ID > 255 AND
 i.index_id <= 1
GROUP BY
 t.NAME, i.object_id, i.index_id, i.name
ORDER BY
 OBJECT_NAME(i.object_id)
<pre>