SQL Server Express 2005 DB over size but data < 1GB

Question

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?

Answer 1

First, check your database for indexed views, they would be missed by your original script (courtesy of Michael J Swart):

select OBJECT_SCHEMA_NAME(object_id) as [SchemaName],
       OBJECT_NAME(object_id) as [ViewName],
       Name as IndexName
from sys.indexes
where object_id in 
  (select object_id from sys.views)

If that list contains any rows, run the following to see how much space each uses:

EXEC sys.sp_spaceused @objname = N'dbo.YourView'

Second, try this script to distinguish between data and index space in each table (a variation of alpav's script here):

create table #tbl(
  name nvarchar(128),
  rows varchar(50),
  reserved varchar(50),
  data varchar(50),
  index_size varchar(50),
  unused varchar(50)
)

exec sp_msforeachtable 'insert into #tbl exec sp_spaceused [?]'

select name, rows,
    convert(int, substring(reserved, 1, len(reserved)-3)) as reserved_kb,
    convert(int, substring(data, 1, len(data)-3)) as data_kb,
    convert(int, substring(index_size, 1, len(index_size)-3)) as index_kb,
    convert(int, substring(unused, 1, len(unused)-3)) as free_kb
 from #tbl
    order by 5 desc

drop table #tbl

That should at least help you determine what table is responsible.

Once you've done that, you'll need to take further action:

1. Drop unneeded tables, indexed views, and indexes
2. Truncate or delete unneeded data
3. Drop and re-create indexes with a higher fill factor
4. Drop and re-create indexes with fewer indexed fields, or fewer included fields.

Beyond that, you'll have to look at the data structure (data types used, redundant data, etc), but that's not an easy fix.

Answer 2

Check the data file size, the limit applies to the data file size

select name, physical_name, size/128 "Size in MB" from sys.database_files where type=0

Answer 3

I have used this SQL before to find objects and sizes.

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 
    UsedSpaceMB desc