August 9, 2007 at 9:49 am
Hi,
In one of my project database size is very large in comparison to sum of table size, I have updated statistics to get correct table size with query.
SP_Spaceused shows
database size
DB SIZE Unallocated
152022.50 MB 12729.59 MB
Table Size
SELECT 'Table' = convert (varchar (50), o.name), ROWS=i.rows,PAGE=i.dpages, MB = (dpages * 8)/1024
from SYSobjects o, SYSindexes i
where o.type = 'u'
and o.id = i.id
and i.indid in (0,1)
ORDER BY (dpages * 8)/1024 DESC
Sum of table shows 18 GB
with biggest table of 11 GB with showcontig for that table showing
- Pages Scanned................................: 1186843
- Extents Scanned..............................: 149985
- Extent Switches..............................: 161418
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 91.91% [148356:161419]
- Logical Scan Fragmentation ..................: 0.56%
- Extent Scan Fragmentation ...................: 4.33%
- Avg. Bytes Free per Page.....................: 706.7
- Avg. Page Density (full).....................: 91.27%
Checked frag. for other tables too and is above 75 %
Thank you in advance for your time and help.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
August 9, 2007 at 9:58 am
Have you also included the size of the indexes in your calculations?
August 9, 2007 at 10:25 am
This script will show you the size of all tables in the database.
Script to analyze table space usage
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762
Maybe you just have a lot of empty space in the database. This script will show you how much is used in each file:
-- Show Size, Space Used, Unused Space, and Name of all database files select [FileSizeMB] = convert(numeric(10,2),sum(round(a.size/128.,2))), [UsedSpaceMB] = convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/128.,2))) , [UnusedSpaceMB] = convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))) , [DBFileName] = isnull(a.name,'Total for all files') from sysfiles a group by a.name with rollup order by case when a.name is null then 2 else 1 end, a.name
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply