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 Space

Hello, All!
I'm creating a script to collect DataBase Space used and DataBase space Total
and after that I will store this information in a table in my SQL Server.
I've tryed using sp_spaceused but I don't know how to use the result of this
StoreProcedure.
If anyone know how to get this information using either sp_spaceused results
or t-sql script example, it would be great for me.
Thanks
Juliano Horta
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200602/1> I'm creating a script to collect DataBase Space used and DataBase space
> Total
> and after that I will store this information in a table in my SQL Server.
> I've tryed using sp_spaceused but I don't know how to use the result of
> this
> StoreProcedure.
> If anyone know how to get this information using either sp_spaceused
> results
> or t-sql script example, it would be great for me.
Why don't you look at the source code for sp_spaceused and adapt it for your
own needs?|||Use the undocumented command DBCC ShowFileStats. It supports the WITH
TABLERESULTS option so you can dump the data into a table and work with it.
This is what Enterprise Mangler uses to populate the file used graphs on the
Taskpad pane.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Juliano H via SQLMonster.com" <u13014@.uwe> wrote in message
news:5b3d9946b1a22@.uwe...
> Hello, All!
> I'm creating a script to collect DataBase Space used and DataBase space
> Total
> and after that I will store this information in a table in my SQL Server.
> I've tryed using sp_spaceused but I don't know how to use the result of
> this
> StoreProcedure.
> If anyone know how to get this information using either sp_spaceused
> results
> or t-sql script example, it would be great for me.
> Thanks
> Juliano Horta
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200602/1|||Hi,
Try this to see whats in sp_spaceused...
sp_helptext 'sp_spaceused'
Thanks,
Sree
"Geoff N. Hiten" wrote:
> Use the undocumented command DBCC ShowFileStats. It supports the WITH
> TABLERESULTS option so you can dump the data into a table and work with it.
> This is what Enterprise Mangler uses to populate the file used graphs on the
> Taskpad pane.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Juliano H via SQLMonster.com" <u13014@.uwe> wrote in message
> news:5b3d9946b1a22@.uwe...
> > Hello, All!
> >
> > I'm creating a script to collect DataBase Space used and DataBase space
> > Total
> > and after that I will store this information in a table in my SQL Server.
> > I've tryed using sp_spaceused but I don't know how to use the result of
> > this
> > StoreProcedure.
> > If anyone know how to get this information using either sp_spaceused
> > results
> > or t-sql script example, it would be great for me.
> >
> > Thanks
> >
> > Juliano Horta
> >
> > --
> > Message posted via SQLMonster.com
> > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200602/1
>
>

Database sizing question

Hi,

I have 1.3 GB ASII file for each month to be tranferred to a table. I want to get 18 months( 18 files) so 18*1.3 GB will be my total file size. Now I am transferring one file at a time to the table using INSERT. I have an index on one column only ( NON CLUSTERED NON UNIQUE). My question is what should be my data file size and transaction log file size for this operation. I will only be doing SELECT and INSERT in this table. Any other SQL server parameters that I need to take care of then please let me know.

ThanksHowdy

Without knowing the table datatypes used in the table columns, its difficult to know. It can be calculated but its time consuming and probably more hassle than you want.

Try this ( its easier and faster ) :

(1) Create the table you are importing data to.

(2) Switch the database into Bulk Insert or Simple recovery mode during the data load ( this will stop the transaction log becoming huge and is good practice anyway during a large data load into a database ).

(3) Once the data is loaded, re-create the index. This will ensure the database is "clean" and the tran logs is as small as possible. During normal database operation ( inserts & updates ), the tran log will grow of course, if the recovery mode is FULL or BULK LOGGED.

(4) Change the database back into FULL recovery mode ( assuming you want to be able to recover the database to apoint in time ) and start using it.

Cheers,

SG.

Database size vs total table and index size

I use SQL Server 2000.
My database is 1912.69 MB with no available free space.
My logfile is 1 MB.
The size of all tables and indexes add up to 200 MB.
I have no diagrams, two views, fifty stored procedures, six users, ten
roles, no rules, no defaults, no user defined data types, no user
defined functions.
Autoshrink is set to true.
My question:
How can the database be almost 2 GB when the tables and indexes add up
to only 200MB?
When I try to shrink manually in SQL Enterprise Manager I get no error
message, but no shrinking occurs.
I am grateful for any help.
Regards,
Jan Nordgreen
Reply"damezumari" wrote:
> I use SQL Server 2000.
> My database is 1912.69 MB with no available free space.
> My logfile is 1 MB.
> The size of all tables and indexes add up to 200 MB.
> I have no diagrams, two views, fifty stored procedures, six users, ten
> roles, no rules, no defaults, no user defined data types, no user
> defined functions.
> Autoshrink is set to true.
> My question:
> How can the database be almost 2 GB when the tables and indexes add up
> to only 200MB?
> When I try to shrink manually in SQL Enterprise Manager I get no error
> message, but no shrinking occurs.
> I am grateful for any help.
> Regards,
> Jan Nordgreen
This sounds VERY unusual: database sizes that are 10 times bigger than the
actual datasize are absolutely normal and there's a lot of reasons for that
-
but they usually have something to do with the TA-Log. Please verify that
your transaction log file is really that tiny, and publish the syntax of you
r
shrink statement ...|||First, I don=B4t think that this is TA related, because your TA size is
1MB which is really , really small.
Your issue could be based on several things like:
You can=B4t shrink the database size under the initial size, so if the
initial size was 2GB (which isn=B4t unusal and not that big) you can=B4t
shrink it with DBCC Shrinkdatabase. Look in the BOL for more
information:
"The target size for data and log files as calculated by DBCC
SHRINKDATABASE can never be smaller than the minimum size of a file.
The minimum size of a file is the size specified when the file was
originally created, or the last explicit size set with a file size
changing operation, such as DBCC SHRINKFILE."
You can shrink the database using DBCC Shrinkfile, where you can
specify a new size of a single file. Look in the BOL for more
information.
Anyway, shrinking your file to a smaller size than 2GB could decrease
performance if your database is growing and gaining automatically new
space. So shrinking the database to 200MB would cause a halt if the
size has to be extended, causing waiting processes to be stopped until
the new size is aquired from the OS:
HTH, Jens Suessmeyer.

Database size vs total table and index size

I use SQL Server 2000.
My database is 1912.69 MB with no available free space.
My logfile is 1 MB.
The size of all tables and indexes add up to 200 MB.
I have no diagrams, two views, fifty stored procedures, six users, ten
roles, no rules, no defaults, no user defined data types, no user
defined functions.
Autoshrink is set to true.
My question:
How can the database be almost 2 GB when the tables and indexes add up
to only 200MB?
When I try to shrink manually in SQL Enterprise Manager I get no error
message, but no shrinking occurs.
I am grateful for any help.
Regards,
Jan Nordgreen
Reply
"damezumari" wrote:
> I use SQL Server 2000.
> My database is 1912.69 MB with no available free space.
> My logfile is 1 MB.
> The size of all tables and indexes add up to 200 MB.
> I have no diagrams, two views, fifty stored procedures, six users, ten
> roles, no rules, no defaults, no user defined data types, no user
> defined functions.
> Autoshrink is set to true.
> My question:
> How can the database be almost 2 GB when the tables and indexes add up
> to only 200MB?
> When I try to shrink manually in SQL Enterprise Manager I get no error
> message, but no shrinking occurs.
> I am grateful for any help.
> Regards,
> Jan Nordgreen
This sounds VERY unusual: database sizes that are 10 times bigger than the
actual datasize are absolutely normal and there's a lot of reasons for that -
but they usually have something to do with the TA-Log. Please verify that
your transaction log file is really that tiny, and publish the syntax of your
shrink statement ...
|||First, I don=B4t think that this is TA related, because your TA size is
1MB which is really , really small.
Your issue could be based on several things like:
You can=B4t shrink the database size under the initial size, so if the
initial size was 2GB (which isn=B4t unusal and not that big) you can=B4t
shrink it with DBCC Shrinkdatabase. Look in the BOL for more
information:
"The target size for data and log files as calculated by DBCC
SHRINKDATABASE can never be smaller than the minimum size of a file.
The minimum size of a file is the size specified when the file was
originally created, or the last explicit size set with a file size
changing operation, such as DBCC SHRINKFILE."
You can shrink the database using DBCC Shrinkfile, where you can
specify a new size of a single file. Look in the BOL for more
information.
Anyway, shrinking your file to a smaller size than 2GB could decrease
performance if your database is growing and gaining automatically new
space. So shrinking the database to 200MB would cause a halt if the
size has to be extended, causing waiting processes to be stopped until
the new size is aquired from the OS:
HTH, Jens Suessmeyer.

Database size vs total table and index size

I use SQL Server 2000.
My database is 1912.69 MB with no available free space.
My logfile is 1 MB.
The size of all tables and indexes add up to 200 MB.
I have no diagrams, two views, fifty stored procedures, six users, ten
roles, no rules, no defaults, no user defined data types, no user
defined functions.
Autoshrink is set to true.
My question:
How can the database be almost 2 GB when the tables and indexes add up
to only 200MB?
When I try to shrink manually in SQL Enterprise Manager I get no error
message, but no shrinking occurs.
I am grateful for any help.
Regards,
Jan Nordgreen
Reply"damezumari" wrote:
> I use SQL Server 2000.
> My database is 1912.69 MB with no available free space.
> My logfile is 1 MB.
> The size of all tables and indexes add up to 200 MB.
> I have no diagrams, two views, fifty stored procedures, six users, ten
> roles, no rules, no defaults, no user defined data types, no user
> defined functions.
> Autoshrink is set to true.
> My question:
> How can the database be almost 2 GB when the tables and indexes add up
> to only 200MB?
> When I try to shrink manually in SQL Enterprise Manager I get no error
> message, but no shrinking occurs.
> I am grateful for any help.
> Regards,
> Jan Nordgreen
This sounds VERY unusual: database sizes that are 10 times bigger than the
actual datasize are absolutely normal and there's a lot of reasons for that -
but they usually have something to do with the TA-Log. Please verify that
your transaction log file is really that tiny, and publish the syntax of your
shrink statement ...|||First, I don=B4t think that this is TA related, because your TA size is
1MB which is really , really small.
Your issue could be based on several things like:
You can=B4t shrink the database size under the initial size, so if the
initial size was 2GB (which isn=B4t unusal and not that big) you can=B4t
shrink it with DBCC Shrinkdatabase. Look in the BOL for more
information:
"The target size for data and log files as calculated by DBCC
SHRINKDATABASE can never be smaller than the minimum size of a file.
The minimum size of a file is the size specified when the file was
originally created, or the last explicit size set with a file size
changing operation, such as DBCC SHRINKFILE."
You can shrink the database using DBCC Shrinkfile, where you can
specify a new size of a single file. Look in the BOL for more
information.
Anyway, shrinking your file to a smaller size than 2GB could decrease
performance if your database is growing and gaining automatically new
space. So shrinking the database to 200MB would cause a halt if the
size has to be extended, causing waiting processes to be stopped until
the new size is aquired from the OS:
HTH, Jens Suessmeyer.

Database size entry?

Which system table is the currently defined size (hopefully the total size)
of the data and log devices found?
I'm assuming in master somewhere? sysobjects? I just can't find it...
thanksDave,
Check out:
sysfiles
HTH
Jerry
"Dave H" <DaveH@.noemail.nospam> wrote in message
news:JpydnanWHeJCj6HeRVn-ug@.comcast.com...
> Which system table is the currently defined size (hopefully the total
> size)
> of the data and log devices found?
> I'm assuming in master somewhere? sysobjects? I just can't find it...
> thanks
>|||That's what I'm doing now.. is that how 'properties' figures the size?
lol: I totally looked past size there, and was just getting the file
names...
Thanks...
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:ODlqHFRxFHA.1256@.TK2MSFTNGP09.phx.gbl...
> Dave,
> Check out:
> sysfiles
> HTH
> Jerry
> "Dave H" <DaveH@.noemail.nospam> wrote in message
> news:JpydnanWHeJCj6HeRVn-ug@.comcast.com...
>|||Hi,
Its been taken from sysfiles table. You could just run a prfiler and get the
query. See the query I get for Master database property.
SELECT o.fileid, o.name, o.filename, o.groupid, o.size, o.maxsize, o.growth,
o.status FROM dbo.sysfiles o WHERE o.groupid = (SELECT u.groupid FROM
dbo.sysfilegroups u WHERE u.groupname = N'PRIMARY') and (o.status & 0x40) =
0
go
SELECT fileid, name, filename, size, growth, status, maxsize FROM
dbo.sysfiles WHERE (status & 0x40) <> 0
Thanks
Hari
SQL Server MVP
"Dave H" <DaveH@.noemail.nospam> wrote in message
news:ec-dnRXjTNF0iKHeRVn-rw@.comcast.com...
> That's what I'm doing now.. is that how 'properties' figures the size?
> lol: I totally looked past size there, and was just getting the file
> names...
> Thanks...
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:ODlqHFRxFHA.1256@.TK2MSFTNGP09.phx.gbl...
>

Database Size

Hi,
Someone can telle how with a query can i get the use size of all my db of akll my server.
I use the table sysfiles but is the total size of my file and not the use size.
Thanks a lot and happy new year.
Best regards.Would looking at all the *.mdf/*.ldf files where you store them help?|||This question has been addressed a couple of times recently. Here's a link...

http://www.dbforums.com/t1006334.html

Regards,

hmscottsql