I have a SQL Express 2005 instance which is not working correctly due to it hitting the 4GB DB size limit. I have looked at the data in the DB and cannot see how this is possible – the data is less than 1GB.
I checked with this query:
SELECT
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
sum(a.total_pages) DESC
The output of this query shows the following table sizes:
105370,32028,32015,839,250,250,6
601107,11866,11857,11811,92,92,92
636064,11666,11088,10150,91,86,79
350950,3458,3377,3360,27,26,26
146825,2001,1980,1971,15,15,15
118641,1881,1837,1829,14,14,14
68834,1778,1756,1747,13,13,13
79954,1107,1051,570,8,8,4
87993,1025,1006,1001,8,7,7
63738,801,793,789,6,6,6
591,719,718,3,5,5,0
29614,404,395,158,3,3,1
43082,401,378,376,3,2,2
13314,275,230,86,2,1,0
20982,218,170,157,1,1,1
476,86,80,3,0,0,0
2382,82,66,20,0,0,0
634,74,60,53,0,0,0
There are more tables but their size is all zero from here downwards.
Where is the additional space going? I would delete some records but it seems there is a large amount of space being lost somewhere. I have tried compacting and shrinking the DB to no avail.
EDIT: sp_spaceused reports the following:
Reserved Data Index size Unused
4194152 KB 552584 KB 3523920 KB 117648 KB
So it appears the additional space is being used by the index?
Best Answer
First, check your database for indexed views, they would be missed by your original script (courtesy of Michael J Swart):
If that list contains any rows, run the following to see how much space each uses:
Second, try this script to distinguish between data and index space in each table (a variation of alpav's script here):
That should at least help you determine what table is responsible.
Once you've done that, you'll need to take further action:
Beyond that, you'll have to look at the data structure (data types used, redundant data, etc), but that's not an easy fix.