1

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?

6
  • what does sp_spaceused report?
    – paparazzo
    Feb 14, 2017 at 16:10
  • Please review this article regarding shrinking: brentozar.com/blitz/auto-shrink-enabled
    – SQLDevDBA
    Feb 14, 2017 at 16:12
  • I have updated the original post with output from sp_spaceused
    – btongeorge
    Feb 14, 2017 at 18:39
  • Is upgrading to a newer (supported) version of SQL Server on the table? Higher size limits and more features will open up many solutions.
    – AMtwo
    Feb 14, 2017 at 20:45
  • 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
    – btongeorge
    Feb 14, 2017 at 20:50

3 Answers 3

2

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.

1
  • 1
    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 :-)
    – btongeorge
    Feb 17, 2017 at 18:30
0

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

2
  • 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.
    – btongeorge
    Feb 14, 2017 at 18:50
  • I have used this SQL before to find objects and sizes
    – JRGWV
    Feb 14, 2017 at 18:57
0

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
1
  • 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
    – btongeorge
    Feb 14, 2017 at 19:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.