Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

sp_spaceused

5 posts in General Discussion Last posting was on 2011-10-25 16:10:29.0Z
tom-tom Posted on 2011-10-24 09:46:42.0Z
Sender: 2459.4ea53322.1804289383@sybase.com
From: tom-tom
Newsgroups: sybase.public.ase.general
Subject: sp_spaceused
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ea53401.2496.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 Oct 2011 02:46:42 -0700
X-Trace: forums-1-dub 1319449602 10.22.241.41 (24 Oct 2011 02:46:42 -0700)
X-Original-Trace: 24 Oct 2011 02:46:42 -0700, 10.22.241.41
Lines: 8
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30642
Article PK: 73533

Hi

I was using sp_spaceused command for a 5 mb database, did
not understand the output. Thanks

reserved data index_size unused
--------- --------- ----------- --------
2176 KB 1374 KB 72 KB 730 KB


Rob V Posted on 2011-10-24 10:00:35.0Z
From: Rob V <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: sp_spaceused
References: <4ea53401.2496.1681692777@sybase.com>
In-Reply-To: <4ea53401.2496.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ea53743@forums-1-dub>
Date: 24 Oct 2011 03:00:35 -0700
X-Trace: forums-1-dub 1319450435 10.22.241.152 (24 Oct 2011 03:00:35 -0700)
X-Original-Trace: 24 Oct 2011 03:00:35 -0700, vip152.sybase.com
Lines: 35
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30643
Article PK: 73534


On 24-Oct-2011 11:46, tom-tom wrote:
> Hi
>
> I was using sp_spaceused command for a 5 mb database, did
> not understand the output. Thanks
>
> reserved data index_size unused
> --------- --------- ----------- --------
> 2176 KB 1374 KB 72 KB 730 KB

You have ~2 MB used (that's the 'reserved' number), about 1.3 MB is
data, 72 KB is index.
The 730 KB unused space is part of the allocated space for the table and
index, but this 730 KB is actually not yet occupied by any data (though
it has been assigned to specific tables or indexes).

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


tom-tom Posted on 2011-10-24 10:52:44.0Z
Sender: 2459.4ea53322.1804289383@sybase.com
From: tom-tom
Newsgroups: sybase.public.ase.general
Subject: Re: sp_spaceused
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ea5437c.296b.1681692777@sybase.com>
References: <4ea53743@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 Oct 2011 03:52:44 -0700
X-Trace: forums-1-dub 1319453564 10.22.241.41 (24 Oct 2011 03:52:44 -0700)
X-Original-Trace: 24 Oct 2011 03:52:44 -0700, 10.22.241.41
Lines: 50
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30644
Article PK: 73535


> On 24-Oct-2011 11:46, tom-tom wrote:
> > Hi
> >
> > I was using sp_spaceused command for a 5 mb database,
> > did not understand the output. Thanks
> >
> > reserved data index_size unused
> > --------- --------- ----------- --------
> > 2176 KB 1374 KB 72 KB 730 KB
>
> You have ~2 MB used (that's the 'reserved' number), about
> 1.3 MB is data, 72 KB is index.
> The 730 KB unused space is part of the allocated space for
> the table and index, but this 730 KB is actually not yet
> occupied by any data (though it has been assigned to
> specific tables or indexes).
>
> --
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Sybase Professional DBA for ASE
> 15.0/12.5/12.0/11.5/11.0 and Replication Server
> 15.0.1/12.5 // TeamSybase
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
> Reference Guide" "The Complete Sybase Replication Server
> Quick Reference Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------

Hi Rob,

Thanks for the reply, while i was checking the space via
sybase central, I noticed that there is a 'reserved' and
'unreserved' space columns. The 'reserved' part I assume
would be the combination of data, index and unused space.
But what about the 'unreserve' space? Combining both is the
total size of the DB including the logs? (data and log in
different devices). Thanks again.
>


Rob V Posted on 2011-10-24 10:55:36.0Z
From: Rob V <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: sp_spaceused
References: <4ea53743@forums-1-dub> <4ea5437c.296b.1681692777@sybase.com>
In-Reply-To: <4ea5437c.296b.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ea54428$1@forums-1-dub>
Date: 24 Oct 2011 03:55:36 -0700
X-Trace: forums-1-dub 1319453736 10.22.241.152 (24 Oct 2011 03:55:36 -0700)
X-Original-Trace: 24 Oct 2011 03:55:36 -0700, vip152.sybase.com
Lines: 73
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30645
Article PK: 73536


On 24-Oct-2011 12:52, tom-tom wrote:
>> On 24-Oct-2011 11:46, tom-tom wrote:
>>> Hi
>>>
>>> I was using sp_spaceused command for a 5 mb database,
>>> did not understand the output. Thanks
>>>
>>> reserved data index_size unused
>>> --------- --------- ----------- --------
>>> 2176 KB 1374 KB 72 KB 730 KB
>> You have ~2 MB used (that's the 'reserved' number), about
>> 1.3 MB is data, 72 KB is index.
>> The 730 KB unused space is part of the allocated space for
>> the table and index, but this 730 KB is actually not yet
>> occupied by any data (though it has been assigned to
>> specific tables or indexes).
>>
>> --
>> HTH,
>>
>> Rob V.
>> ----------------------------------------------------------
>> ------- Rob Verschoor
>>
>> Certified Sybase Professional DBA for ASE
>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
>> 15.0.1/12.5 // TeamSybase
>>
>> Author of Sybase books (order online at
>> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
>> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
>> Reference Guide" "The Complete Sybase Replication Server
>> Quick Reference Guide"
>>
>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>> Commerce 27138666
>> ----------------------------------------------------------
>> -------
> Hi Rob,
>
> Thanks for the reply, while i was checking the space via
> sybase central, I noticed that there is a 'reserved' and
> 'unreserved' space columns. The 'reserved' part I assume
> would be the combination of data, index and unused space.
> But what about the 'unreserve' space? Combining both is the
> total size of the DB including the logs? (data and log in
> different devices). Thanks again.

That could well be the case if it all adds up to your total database
size... Sybase Central does not simply show the result of procedures
like sp_spaceused, but does its own calculations/presentations on top of
it. I don;t really used SybCentral so much for these things...

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Bret Halford Posted on 2011-10-25 16:10:29.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: sp_spaceused
References: <4ea53743@forums-1-dub> <4ea5437c.296b.1681692777@sybase.com>
In-Reply-To: <4ea5437c.296b.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ea6df75$1@forums-1-dub>
Date: 25 Oct 2011 09:10:29 -0700
X-Trace: forums-1-dub 1319559029 10.22.241.152 (25 Oct 2011 09:10:29 -0700)
X-Original-Trace: 25 Oct 2011 09:10:29 -0700, vip152.sybase.com
Lines: 36
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30648
Article PK: 73538


> Hi Rob,
>
> Thanks for the reply, while i was checking the space via
> sybase central, I noticed that there is a 'reserved' and
> 'unreserved' space columns. The 'reserved' part I assume
> would be the combination of data, index and unused space.
> But what about the 'unreserve' space? Combining both is the
> total size of the DB including the logs? (data and log in
> different devices). Thanks again.
>>

I would expect so as well. Does it not total to the size
of the database?

I'll also add a comment about understanding sp_spaceused
output, I don't think you are hitting it but people are often
confused the first time they run it on a table that has
text/image column(s). The space used for the text/image
data is by default reported under "index" (the data
is stored in a page chain with index id 255, not as part
of the base data which is index id 0). As a result, the
output can show the index size being relatively huge
compared to the data size.

The solution to this confusion is to run the procedure with
the optional second parameter "1".
sp_spaceused tablename, 1

this breaks out the size allocated to the data and
each individual index, including text/image. The text/image
data has an "index name" of "t_"+tablename.

Cheers,
-bret