Database Space Used

I'm trying to report the amount of space allocated and used for each
database. I use sysfiles to report the total space allocated to a database,
but can't find information regarding how much of that space is has been used.
I want to store that information in a table each week/month to chart growth.
Is there a system table that stores how much space of each datafile/database
is being used?
Thanks. Any help would be appreciated.
Ron
You can get that information from the stored procedure sp_spaceused. That
just sums the space used and reserved for the tables and indexes in the
database from sysindexes. You can study the code of sp_spaceused (it's in
the master database), but what you want is basically:
SELECT SUM(reserved)
FROM sysindexes
WHERE indid in (0, 1, 255)
Jacco Schalkwijk
SQL Server MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:99CE9BA6-3B46-434B-B769-17FBD8BE4C9C@.microsoft.com...
> I'm trying to report the amount of space allocated and used for each
> database. I use sysfiles to report the total space allocated to a
> database,
> but can't find information regarding how much of that space is has been
> used.
> I want to store that information in a table each week/month to chart
> growth.
> Is there a system table that stores how much space of each
> datafile/database
> is being used?
> Thanks. Any help would be appreciated.
> Ron
>
|||There is an undocumented DBCC command 'DBCC SHOWFILESTATS' that returns
information on allocations per file. You can write a simple wrapper that
aggregates per filegroup or database (or both).
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:99CE9BA6-3B46-434B-B769-17FBD8BE4C9C@.microsoft.com...
> I'm trying to report the amount of space allocated and used for each
> database. I use sysfiles to report the total space allocated to a
database,
> but can't find information regarding how much of that space is has been
used.
> I want to store that information in a table each week/month to chart
growth.
> Is there a system table that stores how much space of each
datafile/database
> is being used?
> Thanks. Any help would be appreciated.
> Ron
>