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?