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
>

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.
RonYou 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
>

Database Size - Allocated v. Used - Strange Problem

Ok, I am seeing something weird.
I have a production database that according to EM and DBCC SHOWFILESTATS
has 140GB of space allocated and is currently using 138GB. However,
according to the OS, the data file is ~ 157GB. I also perform log
shipping with this DB and the size of the data file on the standby copy is
correct @. 140GB. This tells me that the number OS seen size on the
production server is definitely incorrect. How can this be? What could
cause this sort of discrepancy?
The only thing that makes even a bit of sense.... the last mod date on
the data file is last week around the time I had a stupid vendor kick off
a select * into from a table with 35 million records. It is possible that
this caused an auto grow. The transaction never committed as I caught it
and rolled it back. Could the growth on the OS side have "stuck". The
numbers jive somewhat with what the auto growth rate is set at.
Regardless I am not ready to give up that much additional space right now,
especially not knowing if the OS/SQL are chatting correctly, therefore
making what the OS sees actually available to SQL.
Any input would be greatly appreciated.
Thanks!
PaulWell I think I fixed it.
I grew the data file by 1MB through EM, and the file size value in the OS
seemed to "reset" itself.
Strange.
Paul
"Paul Timmerman" wrote:
> Ok, I am seeing something weird.
> I have a production database that according to EM and DBCC SHOWFILESTATS
> has 140GB of space allocated and is currently using 138GB. However,
> according to the OS, the data file is ~ 157GB. I also perform log
> shipping with this DB and the size of the data file on the standby copy is
> correct @. 140GB. This tells me that the number OS seen size on the
> production server is definitely incorrect. How can this be? What could
> cause this sort of discrepancy?
> The only thing that makes even a bit of sense.... the last mod date on
> the data file is last week around the time I had a stupid vendor kick off
> a select * into from a table with 35 million records. It is possible that
> this caused an auto grow. The transaction never committed as I caught it
> and rolled it back. Could the growth on the OS side have "stuck". The
> numbers jive somewhat with what the auto growth rate is set at.
> Regardless I am not ready to give up that much additional space right now,
> especially not knowing if the OS/SQL are chatting correctly, therefore
> making what the OS sees actually available to SQL.
> Any input would be greatly appreciated.
> Thanks!
> Paul
>|||Try DBCC UPDATEUSAGE next time and see if that helps.
--
Andrew J. Kelly SQL MVP
"Paul Timmerman" <PaulTimmerman@.discussions.microsoft.com> wrote in message
news:75C0B5A4-A4EF-48F0-B4C2-BB4F22C9F0E1@.microsoft.com...
> Well I think I fixed it.
> I grew the data file by 1MB through EM, and the file size value in the OS
> seemed to "reset" itself.
> Strange.
> Paul
> "Paul Timmerman" wrote:
>> Ok, I am seeing something weird.
>> I have a production database that according to EM and DBCC SHOWFILESTATS
>> has 140GB of space allocated and is currently using 138GB. However,
>> according to the OS, the data file is ~ 157GB. I also perform log
>> shipping with this DB and the size of the data file on the standby copy
>> is
>> correct @. 140GB. This tells me that the number OS seen size on the
>> production server is definitely incorrect. How can this be? What could
>> cause this sort of discrepancy?
>> The only thing that makes even a bit of sense.... the last mod date on
>> the data file is last week around the time I had a stupid vendor kick off
>> a select * into from a table with 35 million records. It is possible that
>> this caused an auto grow. The transaction never committed as I caught it
>> and rolled it back. Could the growth on the OS side have "stuck". The
>> numbers jive somewhat with what the auto growth rate is set at.
>> Regardless I am not ready to give up that much additional space right
>> now,
>> especially not knowing if the OS/SQL are chatting correctly, therefore
>> making what the OS sees actually available to SQL.
>> Any input would be greatly appreciated.
>> Thanks!
>> Paulsql

Database Size - Allocated v. Used - Strange Problem

Ok, I am seeing something weird.
I have a production database that according to EM and DBCC SHOWFILESTATS
has 140GB of space allocated and is currently using 138GB. However,
according to the OS, the data file is ~ 157GB. I also perform log
shipping with this DB and the size of the data file on the standby copy is
correct @. 140GB. This tells me that the number OS seen size on the
production server is definitely incorrect. How can this be? What could
cause this sort of discrepancy?
The only thing that makes even a bit of sense.... the last mod date on
the data file is last week around the time I had a stupid vendor kick off
a select * into from a table with 35 million records. It is possible that
this caused an auto grow. The transaction never committed as I caught it
and rolled it back. Could the growth on the OS side have "stuck". The
numbers jive somewhat with what the auto growth rate is set at.
Regardless I am not ready to give up that much additional space right now,
especially not knowing if the OS/SQL are chatting correctly, therefore
making what the OS sees actually available to SQL.
Any input would be greatly appreciated.
Thanks!
PaulWell I think I fixed it.
I grew the data file by 1MB through EM, and the file size value in the OS
seemed to "reset" itself.
Strange.
Paul
"Paul Timmerman" wrote:

> Ok, I am seeing something weird.
> I have a production database that according to EM and DBCC SHOWFILESTATS
> has 140GB of space allocated and is currently using 138GB. However,
> according to the OS, the data file is ~ 157GB. I also perform log
> shipping with this DB and the size of the data file on the standby copy is
> correct @. 140GB. This tells me that the number OS seen size on the
> production server is definitely incorrect. How can this be? What could
> cause this sort of discrepancy?
> The only thing that makes even a bit of sense.... the last mod date on
> the data file is last week around the time I had a stupid vendor kick off
> a select * into from a table with 35 million records. It is possible that
> this caused an auto grow. The transaction never committed as I caught it
> and rolled it back. Could the growth on the OS side have "stuck". The
> numbers jive somewhat with what the auto growth rate is set at.
> Regardless I am not ready to give up that much additional space right now,
> especially not knowing if the OS/SQL are chatting correctly, therefore
> making what the OS sees actually available to SQL.
> Any input would be greatly appreciated.
> Thanks!
> Paul
>|||Try DBCC UPDATEUSAGE next time and see if that helps.
Andrew J. Kelly SQL MVP
"Paul Timmerman" <PaulTimmerman@.discussions.microsoft.com> wrote in message
news:75C0B5A4-A4EF-48F0-B4C2-BB4F22C9F0E1@.microsoft.com...[vbcol=seagreen]
> Well I think I fixed it.
> I grew the data file by 1MB through EM, and the file size value in the OS
> seemed to "reset" itself.
> Strange.
> Paul
> "Paul Timmerman" wrote:
>

Database Size - Allocated v. Used - Strange Problem

Ok, I am seeing something weird.
I have a production database that according to EM and DBCC SHOWFILESTATS
has 140GB of space allocated and is currently using 138GB. However,
according to the OS, the data file is ~ 157GB. I also perform log
shipping with this DB and the size of the data file on the standby copy is
correct @. 140GB. This tells me that the number OS seen size on the
production server is definitely incorrect. How can this be? What could
cause this sort of discrepancy?
The only thing that makes even a bit of sense.... the last mod date on
the data file is last week around the time I had a stupid vendor kick off
a select * into from a table with 35 million records. It is possible that
this caused an auto grow. The transaction never committed as I caught it
and rolled it back. Could the growth on the OS side have "stuck". The
numbers jive somewhat with what the auto growth rate is set at.
Regardless I am not ready to give up that much additional space right now,
especially not knowing if the OS/SQL are chatting correctly, therefore
making what the OS sees actually available to SQL.
Any input would be greatly appreciated.
Thanks!
Paul
Well I think I fixed it.
I grew the data file by 1MB through EM, and the file size value in the OS
seemed to "reset" itself.
Strange.
Paul
"Paul Timmerman" wrote:

> Ok, I am seeing something weird.
> I have a production database that according to EM and DBCC SHOWFILESTATS
> has 140GB of space allocated and is currently using 138GB. However,
> according to the OS, the data file is ~ 157GB. I also perform log
> shipping with this DB and the size of the data file on the standby copy is
> correct @. 140GB. This tells me that the number OS seen size on the
> production server is definitely incorrect. How can this be? What could
> cause this sort of discrepancy?
> The only thing that makes even a bit of sense.... the last mod date on
> the data file is last week around the time I had a stupid vendor kick off
> a select * into from a table with 35 million records. It is possible that
> this caused an auto grow. The transaction never committed as I caught it
> and rolled it back. Could the growth on the OS side have "stuck". The
> numbers jive somewhat with what the auto growth rate is set at.
> Regardless I am not ready to give up that much additional space right now,
> especially not knowing if the OS/SQL are chatting correctly, therefore
> making what the OS sees actually available to SQL.
> Any input would be greatly appreciated.
> Thanks!
> Paul
>
|||Try DBCC UPDATEUSAGE next time and see if that helps.
Andrew J. Kelly SQL MVP
"Paul Timmerman" <PaulTimmerman@.discussions.microsoft.com> wrote in message
news:75C0B5A4-A4EF-48F0-B4C2-BB4F22C9F0E1@.microsoft.com...[vbcol=seagreen]
> Well I think I fixed it.
> I grew the data file by 1MB through EM, and the file size value in the OS
> seemed to "reset" itself.
> Strange.
> Paul
> "Paul Timmerman" wrote: