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?

• paparazzo: what does sp_spaceused report?
• SQLDevDBA: Please review this article regarding shrinking: brentozar.com/blitz/auto-shrink-enabled
• btongeorge: I have updated the original post with output from sp_spaceused
• anon: Is upgrading to a newer (supported) version of SQL Server on the table? Higher size limits and more features will open up many solutions.
• btongeorge: I restored the DB on an instance of SQL Server Express 2008 R2 and have rebuilt the indexes - just need to restore into the original server now

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.

• btongeorge: This really helped me solve my issue - the second script (sourced from alpav) showed which tables had huge indexes, I then deleted some old records and was able to rebuild the indexes, saving space. Thanks v much :-)

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

• btongeorge: The file size is 4096MB - it is definitely oversized. As noted in the edit to the question it appears the index is taking up the extra space.
• JRGWV: I have used this SQL before to find objects and sizes

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

• btongeorge: Thanks but this is the same query I posted in my question :-/ I know the DB is oversized, its the index causing it, just not how to solve this