How do I select a column from sp_spaceused into a local variable?

[Robin Tucker]

I want to look at the size of the current database, so I can create a new
one if it gets too big (we are working around the 2gb MSDE limit for our
customers).

I would like to do something like this:

DECLARE @size INTEGER

execute BLOB0000.dbo.sp _spaceused

and make @size = the database_size column value that sp_spaceused returns.

Any way to do this?

Thanks.

[Simon Hayes]

Re: How do I select a column from sp_spaceused into a local variable?


"Robin Tucker" <idontwanttobes pammedanymore@r eallyidont.com> wrote in
message news:bkn36q$dh$ 1$8300dec7@news .demon.co.uk...[color=blue]
> I want to look at the size of the current database, so I can create a new
> one if it gets too big (we are working around the 2gb MSDE limit for our
> customers).
>
> I would like to do something like this:
>
> DECLARE @size INTEGER
>
> execute BLOB0000.dbo.sp _spaceused
>
> and make @size = the database_size column value that sp_spaceused returns.
>
> Any way to do this?
>
> Thanks.
>
>
>[/color]

sp_spaceused returns two result sets, so there's no easy way to handle that
in pure SQL. You could handle it on the client side, or alternatively use
sp_helpdb, which only returns one result set if you don't pass a database
name:

create table dbo.DBs (
dbname sysname,
db_size varchar(20),
owner sysname,
dbid int,
created datetime,
status varchar(1000),
compat int
)
go

insert into dbo.DBs
exec sp_helpdb
go

select cast(replace(db _size, 'MB', '') as decimal(10,2))
from dbo.DBs
where dbname = 'BLOB0000'
go

You can also look into the source code of sp_spaceused/sp_helpdb and create
your own proc based on it, or perhaps use SQLDMO to get the database size.

Simon

[Erland Sommarskog]

Re: How do I select a column from sp_spaceused into a local variable?

Robin Tucker (idontwanttobes pammedanymore@r eallyidont.com) writes:[color=blue]
> I want to look at the size of the current database, so I can create a new
> one if it gets too big (we are working around the 2gb MSDE limit for our
> customers).
>
> I would like to do something like this:
>
> DECLARE @size INTEGER
>
> execute BLOB0000.dbo.sp _spaceused
>
> and make @size = the database_size column value that sp_spaceused returns.
>
> Any way to do this?
>[/color]

As Simon said, this is a little tricky, since sp_spaceused returns
two result sets. However, This simple SELECT appears to return the
value you are looking for:

select 8192 * sum(reserved) /1024 from sysindexes
where indid in (1, 0, 255)

You may want to run DBCC UPDATESUAGE prior to running the SELECT to
get accurate numbers.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Manoj Rajshekar]

Re: How do I select a column from sp_spaceused into a local variable?

Hello

check this out. Instead of sp_spaceused you could also use sp_helpdb
to get the size of the DB. With the following code you could get the
size of a DB whatever you are looking for.
=============== =======
set nocount on
declare @size varchar(40)
create table #spaceused (name nvarchar(24), db_size nvarchar(13),
owner nvarchar(24), dbid smallint, created char(11), status
varchar(340),
compatibility_l evel tinyint
)
insert into #spaceused exec sp_helpdb
select @size = db_size from #spaceused where name = 'iserve'
drop table #spaceused
select @size
=============== =======

Regards,
-Manoj Rajshekar

[Robin Tucker]

Re: How do I select a column from sp_spaceused into a local variable?

Ok, yes I can see this, but I don't like to mess around with sys tables for
obvious reasons (they change). I guess I don't have much choice anyway.
Thanks

"Erland Sommarskog" <sommar@algonet .se> wrote in message
news:Xns93FF1B0 C58E0Yazorman@1 27.0.0.1...[color=blue]
> Robin Tucker (idontwanttobes pammedanymore@r eallyidont.com) writes:[color=green]
> > I want to look at the size of the current database, so I can create a[/color][/color]
new[color=blue][color=green]
> > one if it gets too big (we are working around the 2gb MSDE limit for our
> > customers).
> >
> > I would like to do something like this:
> >
> > DECLARE @size INTEGER
> >
> > execute BLOB0000.dbo.sp _spaceused
> >
> > and make @size = the database_size column value that sp_spaceused[/color][/color]
returns.[color=blue][color=green]
> >
> > Any way to do this?
> >[/color]
>
> As Simon said, this is a little tricky, since sp_spaceused returns
> two result sets. However, This simple SELECT appears to return the
> value you are looking for:
>
> select 8192 * sum(reserved) /1024 from sysindexes
> where indid in (1, 0, 255)
>
> You may want to run DBCC UPDATESUAGE prior to running the SELECT to
> get accurate numbers.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet. se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[Erland Sommarskog]

Re: How do I select a column from sp_spaceused into a local variable?

Robin Tucker (idontwanttobes pammedanymore@r eallyidont.com) writes:[color=blue]
> Ok, yes I can see this, but I don't like to mess around with sys tables
> for obvious reasons (they change). I guess I don't have much choice
> anyway.[/color]

There exists a misconception about the system tables. You often see
people say: "don't use the system tables, because Microsoft may change
them".

But things are not really that. The system tables are part of a published
interface, and while it has happened that Microsoft has changed docuemented
functionality without warning, this can happen with any piece in SQL Server,
not just the system tables. However, most often when Microsoft decides
to drop something, they first deprecate it one version of SQL Server,
and in some later version they remove the functionality completely. (Those
changed without warning usually pertained to smaller details.)

What is important to understand about the system tables is that:
o Not all system tables are documented.
o Many columns in documented system tables are described as "reserved"
or "For internal use only".

If you rely on values in undocumented columns or tables, you are walking
a unsafe path.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Re: How do I select a column from sp_spaceused into a local variable?

Manoj Rajshekar (manrajshekar@y ahoo.com) writes:[color=blue]
> check this out. Instead of sp_spaceused you could also use sp_helpdb
> to get the size of the DB. With the following code you could get the
> size of a DB whatever you are looking for.
>============== ========
> set nocount on
> declare @size varchar(40)
> create table #spaceused (name nvarchar(24), db_size nvarchar(13),
> owner nvarchar(24), dbid smallint, created char(11), status
> varchar(340),
> compatibility_l evel tinyint
> )
> insert into #spaceused exec sp_helpdb
> select @size = db_size from #spaceused where name = 'iserve'
> drop table #spaceused
> select @size
>============== ========[/color]

However, sp_helpdb does not return the same value. sp_helpdb returns
the size of the database file, which includes any unallocated space.
sp_spaceused returns the size of the allocated extents. Which data
you want depends. If you are looking to finding databases you can
shrink, you certainly need the reserved column in sp_spaceused.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp