Why is the sum of allocation_units always less than the physical mdf file size?

Question

Why is the sum of allocation_units always less than the physical mdf file size?

mzaremba

Valued Member

Points: 61
More actions
July 22, 2011 at 3:53 pm

#244558

Hello,
I'm working on a way to anticipate when a database is nearing the threshold for autogrowth to occur.
In comparing the sys.database_files to the sum of of the [total_pages] on the sys.allocation_units table, I find that for all our databases the reserved space is less than the physical file size. In some cases the size difference is over 10 gigs (our databases are in the 200 gig range for the file groups).
Can anyone help me to understand why these two methods of tracking the file group sizes are not in agreement?
select
f.data_space_id,
f.name as [FileName],
ltrim(str(f.size * 8192. / 1024.,15,0)) as [CurrentSizeKB],
case f.is_percent_growth
when 1 then 'Percent'
else 'Pages'
end as [GrowthMode],
case f.is_percent_growth
when 0 then ltrim(str(f.growth * 8192. / 1024.,15,0))
else ltrim(str(((f.size * 8192. / 1024.) * (f.growth * 0.01)),15,0))
end as [NextGrowthSizeKB],
(x.ReservedPages * 8192. / 1024.) as [ReservedKB],
(x.UsedPages * 8192. / 1024.) as [UsedKB],
((x.ReservedPages - x.UsedPages) * 8192. / 1024.) as [UnusedKB]
from sys.database_files f
left join (
select
data_space_id,
sum(a.total_pages) as [reservedpages],
sum(a.used_pages) [usedpages],
sum(
CASE
-- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size"
When it.internal_type IN (202,204) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
) as [Pages]
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
group by data_space_id ) x on x.data_space_id = f.data_space_id

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

You're not counting allocation pages with that (GAM, SGAM, ML, DIFF, PFS). Not sure offhand if IAM pages count in sys.partitions.

Are you counting LOB space?

Plus there will be free space within the file.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281344 More actions · Answer 2

If interested, here is a script that should work well for you.

http://jasonbrimhall.info/2010/05/19/tablespace-update/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events