473,890 Members | 1,370 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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.

Jul 20 '05 #1
6 21167

"Robin Tucker" <id************ *************@r eallyidont.com> wrote in
message news:bk******** **********@news .demon.co.uk...
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.


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
Jul 20 '05 #2
Robin Tucker (id************ *************@r eallyidont.com) writes:
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?


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, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
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
Jul 20 '05 #4
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" <so****@algonet .se> wrote in message
news:Xn******** *************@1 27.0.0.1...
Robin Tucker (id************ *************@r eallyidont.com) writes:
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?


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, so****@algonet. se

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

Jul 20 '05 #5
Robin Tucker (id************ *************@r eallyidont.com) writes:
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.


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, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #6
Manoj Rajshekar (ma**********@y ahoo.com) writes:
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
============== ========


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, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
17419
by: Jonathan | last post by:
I'm puzzled by Python's behavior when binding local variables which are introduced within exec() or execfile() statements. First, consider this simple Python program: # main.py def f() : x = 1 print "x:", x f()
2
7535
by: Michelle | last post by:
Hi all, I have a query that scans huge table consists of 8 or more millions records. The funny thing is that if I use the query with local variable, the query takes more than 1 minutes, whereas if I hard code the value into the query, it takes about 1 second. Here are the queries: WITH VARIABLE: -------------------
2
2698
by: Kench | last post by:
I was curious and playing with pointers and references to see what's different between them. Other than the obvious ones involving C++ syntax & things like references cannot be modified with addition & subtraction, pointers can be modified, both are similar. It stated me going what are the diferrence between a reference vairable and local variable ? Here are what I can think of 1. For a local variable constructor is called, not the case...
5
2266
by: FireHead | last post by:
Hello All, Its hard to explain but here it goes: char &free_malloc(char* object_copy){ Here ------------------------------------------------------------------ object_copy is parametre were the contents of the malloc object in main driver module is copied. Which would mean that if I free the object_copy it should not effect
6
5864
by: yasodhai | last post by:
Hi, I used a dropdown control which is binded to a datagrid control. I passed the values to the dropdownlist from the database using a function as follows in the aspx itself. <asp:DropDownList ID="FldType_add" Runat="server" DataSource='< %#GetFieldType()%>' DataValueField="Type" DataTextField="Type" /> Oce the page is loaded all the values are added to the dropdown list. But when I thought of getting the selected value from the...
8
6065
by: jinksto | last post by:
Hello, How do I insert a local variable into a select statement when using the Oracle DB connecter in asp.net 2.0 In Code Behind: I set this the string to be the users id: Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Dim strNTUser As String Dim iPos As Integer strNTUser = RTrim(Request.ServerVariables("LOGON_USER"))
7
6696
by: pauldepstein | last post by:
#include <iostream> using namespace std; double & GetWeeklyHours() { double h = 46.50; double &hours = h; return hours; } //---------------------------------------------------------------------------
25
5253
by: ramprat | last post by:
Hi All, I'm essentially trying to extract a value from the pre_05_growth_factor column of my traffic table and assign it to a variable to be used later. Does anyone know why the code below returns "The growth is 0" when I run this code as part of an on-click event of a button on a form but if I isolate the Select statement and create a query with it in SQL View and then run it I get the correct result showing up from my table? This tells...
1
3332
by: cbjones | last post by:
Can I lable the output column resulting from a SELECT query with a variable from one of the tables which are the source of the data? I know I can hard code the lables in the SELECT statement but since the actual data will not always be “ ½ nuts”, for instance, I want the column to be called whatever is in that field. I am not sure I have been clear here…hoping so! In other words, if I have a table containing Column Name |Data ...
3
2513
by: lenniekuah | last post by:
Hi Good Guy, I am very surprised of the coding generate error message Error 1 Use of unassigned local variable 'strSql' even though the strSQL variable was declared. Not sure what is wrong with the overall coding; Here are the coding:
0
11236
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10830
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9641
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8018
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7172
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6061
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4682
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4276
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3283
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.