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.

estimate free space neecssary for select * into existing table xxx from yyy

3 posts in General Discussion Last posting was on 2013-01-05 18:34:41.0Z
Simon Posted on 2012-12-27 19:00:00.0Z
Sender: 244d.50dc5b4b.1804289383@sybase.com
From: Simon
Newsgroups: sybase.public.ase.general
Subject: estimate free space neecssary for select * into existing table xxx from yyy
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50dc9aaf.2db3.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 27 Dec 2012 11:00:00 -0800
X-Trace: forums-1-dub 1356634800 172.20.134.41 (27 Dec 2012 11:00:00 -0800)
X-Original-Trace: 27 Dec 2012 11:00:00 -0800, 172.20.134.41
Lines: 28
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31617
Article PK: 1158635

Good evening,

I have a the following query that was rollbacked because of
the following message

Can't allocate space for object 'AAA' in database 'BBB'
because 'default' segment is full/has no free extents. If
you ran out of space in syslogs, dump the transaction log.
Otherwise, use ALTER DATABASE to increase the size of the
segment.

the query being executed was:
select * into existing table XXX from YYY

It looks like I need to add extra space in my DB but I'm
wondering how much space should I give?

-if sp_spaceused YYY returns (apologies for the output
format)
YY;500;2000 KB;100 KB;1000 KB;900 KB

Does it means I should have 100KB (data) of free space,
1000KB (index) or 1100KB (data+index) or 2000KB (allocated)
?

Thanks for your tips

Simon


Bret Halford Posted on 2012-12-27 19:23:43.0Z
From: Bret Halford <bret.halford@sap.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:16.0) Gecko/20121026 Thunderbird/16.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: estimate free space neecssary for select * into existing table xxx from yyy
References: <50dc9aaf.2db3.1681692777@sybase.com>
In-Reply-To: <50dc9aaf.2db3.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: <50dca03f$1@forums-1-dub>
Date: 27 Dec 2012 11:23:43 -0800
X-Trace: forums-1-dub 1356636223 172.20.134.152 (27 Dec 2012 11:23:43 -0800)
X-Original-Trace: 27 Dec 2012 11:23:43 -0800, vip152.sybase.com
Lines: 51
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31618
Article PK: 1158636


On 12/27/2012 12:00 PM, Simon wrote:
> Good evening,
>
> I have a the following query that was rollbacked because of
> the following message
>
> Can't allocate space for object 'AAA' in database 'BBB'
> because 'default' segment is full/has no free extents. If
> you ran out of space in syslogs, dump the transaction log.
> Otherwise, use ALTER DATABASE to increase the size of the
> segment.
>
> the query being executed was:
> select * into existing table XXX from YYY
>
> It looks like I need to add extra space in my DB but I'm
> wondering how much space should I give?
>
> -if sp_spaceused YYY returns (apologies for the output
> format)
> YY;500;2000 KB;100 KB;1000 KB;900 KB
>
> Does it means I should have 100KB (data) of free space,
> 1000KB (index) or 1100KB (data+index) or 2000KB (allocated)
> ?
>
> Thanks for your tips
>
> Simon
>

Hi Simon,

The index space on YYY would only be relevant if XXX has the
exact same indexes as YYY. In that case, the (data+index)
value would probably be best (note that YYY is fairly fragmented)

The general approach I'd take would be to run sp_estspace on YYY,
passing the number of rows in XXX to get the estimate of how much
additional space would be needed.

Important note: Prior to 15.5 ESD 4 or 15.7 ESD #1, SELECT INTO
EXISTING will break the transaction log chain in the database
the command is run from even if "select into" is turned off.
This was bug CR 475469.

--
Bret Halford
Support Architect, ASE Tactical Support Team, AGS Primary Support
Sybase, Inc., an SAP Company
385 Interlocken Crescent, Suite 300, Broomfield, Colorado, 80021


Simon Posted on 2013-01-05 18:34:41.0Z
Sender: 773.50e83ea7.1804289383@sybase.com
From: Simon
Newsgroups: sybase.public.ase.general
Subject: Re: estimate free space neecssary for select * into existing table xxx from yyy
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50e8723e.16ed.1681692777@sybase.com>
References: <50dca03f$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 5 Jan 2013 10:34:41 -0800
X-Trace: forums-1-dub 1357410881 172.20.134.41 (5 Jan 2013 10:34:41 -0800)
X-Original-Trace: 5 Jan 2013 10:34:41 -0800, 172.20.134.41
Lines: 61
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31629
Article PK: 1158768

Thank you Bret,

I didn't know the command sp_estspace

It helpmed me


--Simon

> On 12/27/2012 12:00 PM, Simon wrote:
> > Good evening,
> >
> > I have a the following query that was rollbacked because
> > of the following message
> >
> > Can't allocate space for object 'AAA' in database 'BBB'
> > because 'default' segment is full/has no free extents.
> > If you ran out of space in syslogs, dump the transaction
> > log. Otherwise, use ALTER DATABASE to increase the size
> > of the segment.
> >
> > the query being executed was:
> > select * into existing table XXX from YYY
> >
> > It looks like I need to add extra space in my DB but I'm
> > wondering how much space should I give?
> >
> > -if sp_spaceused YYY returns (apologies for the output
> > format)
> > YY;500;2000 KB;100 KB;1000 KB;900 KB
> >
> > Does it means I should have 100KB (data) of free space,
> > 1000KB (index) or 1100KB (data+index) or 2000KB
> > (allocated) ?
> >
> > Thanks for your tips
> >
> > Simon
> >
>
> Hi Simon,
>
> The index space on YYY would only be relevant if XXX has
> the exact same indexes as YYY. In that case, the
> (data+index) value would probably be best (note that YYY
> is fairly fragmented)
>
> The general approach I'd take would be to run sp_estspace
> on YYY, passing the number of rows in XXX to get the
> estimate of how much additional space would be needed.
>
> Important note: Prior to 15.5 ESD 4 or 15.7 ESD #1,
> SELECT INTO EXISTING will break the transaction log chain
> in the database the command is run from even if "select
> into" is turned off. This was bug CR 475469.
>
> --
> Bret Halford
> Support Architect, ASE Tactical Support Team, AGS Primary
> Support Sybase, Inc., an SAP Company
> 385 Interlocken Crescent, Suite 300, Broomfield, Colorado,
> 80021