Wednesday, March 21, 2012

database size

How do I retrieve the size of the database from SQL Server 2000?
I'm expecting that there is a sp for this, but I can't find it.
I don't want to use the size from the filesystem because we are
posting data and the data may not be flushed or the filesystem
data may be otherwise behind and out of date.
Thanks,
Brad.Brad,
sp_helpdb <databasename>
HTH
J
"Brad White" <bwhite at inebraska . com> wrote in message
news:uFqPKcjYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> How do I retrieve the size of the database from SQL Server 2000?
> I'm expecting that there is a sp for this, but I can't find it.
> I don't want to use the size from the filesystem because we are
> posting data and the data may not be flushed or the filesystem
> data may be otherwise behind and out of date.
> --
> Thanks,
> Brad.
>|||Brad White wrote:
> How do I retrieve the size of the database from SQL Server 2000?
> I'm expecting that there is a sp for this, but I can't find it.
> I don't want to use the size from the filesystem because we are
> posting data and the data may not be flushed or the filesystem
> data may be otherwise behind and out of date.
sp_spaceused -- executed from the context of the db you want to check
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi,
Execute the below system procedure to size of all DB;s in a sql server.
sp_databases
Thanks
Hari
SQL Server MVP
"Brad White" <bwhite at inebraska . com> wrote in message
news:uFqPKcjYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> How do I retrieve the size of the database from SQL Server 2000?
> I'm expecting that there is a sp for this, but I can't find it.
> I don't want to use the size from the filesystem because we are
> posting data and the data may not be flushed or the filesystem
> data may be otherwise behind and out of date.
> --
> Thanks,
> Brad.
>|||Thanks for the *fast* answers.
Looks like spaceused only handles internal objects, not whole databases, but
helpdb and databases both return relevant answers.
Now we are thinking it would be to do everything in a stored procedure.
I can retrieve an OUTPUT parameter, or a Return value, but both of these
sp's are returning result sets. I could handle this in ADO, but how do I
retrieve the data from the result
"Brad White" <bwhite at inebraska . com> wrote in message
news:uFqPKcjYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> How do I retrieve the size of the database from SQL Server 2000?
> I'm expecting that there is a sp for this, but I can't find it.
> I don't want to use the size from the filesystem because we are
> posting data and the data may not be flushed or the filesystem
> data may be otherwise behind and out of date.
> --
> Thanks,
> Brad.
>|||oops, it sent accidentally. I'll finish this now. 8:-)
"Brad White" <bwhite at inebraska . com> wrote in message
news:eshb18jYFHA.252@.TK2MSFTNGP12.phx.gbl...
<snip>
I can retrieve an OUTPUT parameter, or a Return value, but both of these
sp's are returning result sets from a select. I could handle this in ADO,
but
how do I retrieve the data from the result set inside another stored
procedure?
"SQL Stored Procedures" in BOL gives examples of the first two cases, but
not of using the result set.
Thanks,
Brad.|||Brad White wrote:
> Thanks for the *fast* answers.
> Looks like spaceused only handles internal objects, not whole
> databases, but helpdb and databases both return relevant answers.
>
sp_spaceused returns database space utilization if executed by itself
from the context of the database you want to check. For example:
database_name database_size unallocated space
-- --
bignw 753.19 MB 176.36 MB
reserved data index_size unused
-- -- -- --
371408 KB 326496 KB 42896 KB 2016 KB
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Read the source code for the procedures and use the same tables etc in your
SELECT statements...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brad White" <bwhite at inebraska . com> wrote in message
news:eshb18jYFHA.252@.TK2MSFTNGP12.phx.gbl...
> Thanks for the *fast* answers.
> Looks like spaceused only handles internal objects, not whole databases, b
ut
> helpdb and databases both return relevant answers.
> Now we are thinking it would be to do everything in a stored procedur
e.
> I can retrieve an OUTPUT parameter, or a Return value, but both of these
> sp's are returning result sets. I could handle this in ADO, but how do I
retrieve the data from
> the result
> --
> "Brad White" <bwhite at inebraska . com> wrote in message
> news:uFqPKcjYFHA.3320@.TK2MSFTNGP12.phx.gbl...
>|||Good guess, I thought of that as well.
The table names all start with #
This from BOL:Create Table,
table_name
Is the name of the new table. Table names must conform to the rules for
identifiers. The combination of owner.table_name must be unique within
the database. table_name can contain a maximum of 128 characters,
except for local temporary table names (names prefixed with a single
number sign (#)) that cannot exceed 116 characters.
says that this makes it a temp table.
and this, from the same section:
A local temporary table created in a stored procedure is dropped
automatically
when the stored procedure completes. The table can be referenced by any
nested stored procedures executed by the stored procedure that created
the
table. The table cannot be referenced by the process which called the
stored
procedure that created the table.
says that I can't access it after the sp exits because it has already been
dropped.
Brad White
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uPciYZpYFHA.2588@.TK2MSFTNGP14.phx.gbl...
> Read the source code for the procedures and use the same tables etc in
> your SELECT statements...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brad White" <bwhite at inebraska . com> wrote in message
> news:eshb18jYFHA.252@.TK2MSFTNGP12.phx.gbl...
>|||I was thinking about doing a deeper analysis of the procedures. They do get
the information from
somewhere, so you can as well. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brad White" <bwhite at inebraska . com> wrote in message
news:OIwzT3rYFHA.2572@.TK2MSFTNGP14.phx.gbl...
> Good guess, I thought of that as well.
> The table names all start with #
> This from BOL:Create Table,
> table_name
> Is the name of the new table. Table names must conform to the rules for
> identifiers. The combination of owner.table_name must be unique within
> the database. table_name can contain a maximum of 128 characters,
> except for local temporary table names (names prefixed with a single
> number sign (#)) that cannot exceed 116 characters.
> says that this makes it a temp table.
> and this, from the same section:
> A local temporary table created in a stored procedure is dropped automa
tically
> when the stored procedure completes. The table can be referenced by any
> nested stored procedures executed by the stored procedure that created
the
> table. The table cannot be referenced by the process which called the s
tored
> procedure that created the table.
> says that I can't access it after the sp exits because it has already been
dropped.
> --
> Brad White
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:uPciYZpYFHA.2588@.TK2MSFTNGP14.phx.gbl...
>

No comments:

Post a Comment