How do you get the actual size of a Database?

How do you get the actual size of SQL Server Database? Quick answer: execute a sp_spaceused command. However, I found this excerpt from Books Online "When updateusage is specified, the SQL Server Database Engine scans the data pages in the database and makes any required corrections…..There are some situations, for example, after an index is dropped, when the space information for the table may not be current." In short, sp_spaceused is not always accurate.

For more accurate calculation of database size, try the following code:

select 
cast(size/128.0 as numeric(10,1)) as [size-mb], 
cast(FILEPROPERTY(name, 'spaceused')/128.0 as numeric(10,1)) as [used-mb], 
maxsize [maximum size-mb], 
cast(growth*8/1024 as varchar(11))+
 case when status&0x100000>0 then '%' else '' end [growth], 
isnull(filegroup_name(groupid),'log') [groupname], 
cast(100*(size/128-(FILEPROPERTY(name,'spaceused')/128))/(size/128.0)
   as int) [% free]
from sysfiles

Here's the result set:

size-mb  used-mb  maximum size-mb growth   groupname  % free
-------- -------- --------------- -------- ---------- -----------
500.0    10.4     -1              64       PRIMARY    98
58.0     5.3      268435456       16       log        91

~~CK