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.

ASe database problem

9 posts in General Discussion Last posting was on 2009-07-09 06:17:22.0Z
CP.L Posted on 2009-07-07 07:11:19.0Z
From: "CP.L" <abc@abc.com>
Newsgroups: sybase.public.ase.general
Subject: ASe database problem
X-Newsreader: newsgroupstats.hk
X-HTTP-Posting-Host: 203.31.32.92
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a52f517$2@forums-3-dub.sybase.com>
Date: 7 Jul 2009 00:11:19 -0700
X-Trace: forums-3-dub.sybase.com 1246950679 10.22.241.152 (7 Jul 2009 00:11:19 -0700)
X-Original-Trace: 7 Jul 2009 00:11:19 -0700, vip152.sybase.com
Lines: 30
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27964
Article PK: 77211

Dear all,

I've installed a ase 12.5 database on my PC (window XP), it's tested okay.

yesterday, when I run a sql, message display as below :-

Can't allocate space for object 'temp worktable' in database 'tempdb' because 'system' 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.

Below is the output result after using sp_helpdb command

device_fragments size usage created free kbytes
mdedat 200.0 MB data only Jun 25 2009 6:04PM 137534
mdelog 80.0 MB log only Jun 25 2009 6:04PM not applicable


mdedat is the data, mdelog is the log

All, can you tell me how to fix it since I'not familiar with ASE database command.

Thanks!



--
Free News Reader
http://put.hk
http://put.hk/reader/forums.sybase.com/sybase.public.ase.general.html


Rob V [Sybase] Posted on 2009-07-07 08:04:49.0Z
Reply-To: "Rob V [Sybase]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob V [Sybase]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.general
References: <4a52f517$2@forums-3-dub.sybase.com>
Subject: Re: ASe database problem
Lines: 78
Organization: Sypron BV / TeamSybase / Sybase Inc
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a5301a1@forums-3-dub.sybase.com>
Date: 7 Jul 2009 01:04:49 -0700
X-Trace: forums-3-dub.sybase.com 1246953889 10.22.241.152 (7 Jul 2009 01:04:49 -0700)
X-Original-Trace: 7 Jul 2009 01:04:49 -0700, vip152.sybase.com
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27965
Article PK: 77212

Your 'tempdb' database is full. Tempdb is used as a work area (or scratch
pad if you like) for various aspects of processing SQL queries.
Tempdb being full is normally caused by one of the following:
- the tempdb database is unrealistically small, for example because the
out-of-the-box size (3 MB) has never been changed, and this could quickly
lead to tempdb filling up, causing the message you've seen.
- the query is accessing a large amount of data, causing a large sort
operation due to -for example- a group-by construct (that's one of the
things a 'temp worktable' is used for)
- if none of the above, it could also be that someone else is doing some
thing out of the ordinary, and consuming the tempdb space (it's shared
between all users), leaving your query with insufficient tempdb space to run

Anyway, please run the following and post the result:

tempdb..sp_helpdb tempdb
go



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"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------

"CP.L" <abc@abc.com> wrote in message
news:4a52f517$2@forums-3-dub.sybase.com...
> Dear all,
>
> I've installed a ase 12.5 database on my PC (window XP), it's tested okay.
>
> yesterday, when I run a sql, message display as below :-
>
> Can't allocate space for object 'temp worktable' in database 'tempdb'
> because 'system' 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.
>
> Below is the output result after using sp_helpdb command
>
> device_fragments size usage created free kbytes
> mdedat 200.0 MB data only Jun 25 2009 6:04PM
> 137534
> mdelog 80.0 MB log only Jun 25 2009 6:04PM not
> applicable
>
>
> mdedat is the data, mdelog is the log
>
> All, can you tell me how to fix it since I'not familiar with ASE database
> command.
>
> Thanks!
>
>
>
> --
> Free News Reader
> http://put.hk
> http://put.hk/reader/forums.sybase.com/sybase.public.ase.general.html
>
>
>


"Mark A. Parsons" <iron_horse Posted on 2009-07-07 10:08:48.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: ASe database problem
References: <4a52f517$2@forums-3-dub.sybase.com> <4a5301a1@forums-3-dub.sybase.com>
In-Reply-To: <4a5301a1@forums-3-dub.sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090706-0, 07/06/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a531eb0$2@forums-3-dub.sybase.com>
Date: 7 Jul 2009 03:08:48 -0700
X-Trace: forums-3-dub.sybase.com 1246961328 10.22.241.152 (7 Jul 2009 03:08:48 -0700)
X-Original-Trace: 7 Jul 2009 03:08:48 -0700, vip152.sybase.com
Lines: 91
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27966
Article PK: 77213

Another possibility is that someone has a long running (ie, 'old') transaction in the tempdb database which is keeping
the log from being truncated. In this case I'd also want to see the results from the following queries:

==================
select getdate()
go
select * from master..syslogshold
go
select * from master..systransactions
go
==================

Rob V [Sybase] wrote:
> Your 'tempdb' database is full. Tempdb is used as a work area (or scratch
> pad if you like) for various aspects of processing SQL queries.
> Tempdb being full is normally caused by one of the following:
> - the tempdb database is unrealistically small, for example because the
> out-of-the-box size (3 MB) has never been changed, and this could quickly
> lead to tempdb filling up, causing the message you've seen.
> - the query is accessing a large amount of data, causing a large sort
> operation due to -for example- a group-by construct (that's one of the
> things a 'temp worktable' is used for)
> - if none of the above, it could also be that someone else is doing some
> thing out of the ordinary, and consuming the tempdb space (it's shared
> between all users), leaving your query with insufficient tempdb space to run
>
> Anyway, please run the following and post the result:
>
> tempdb..sp_helpdb tempdb
> go
>
>
>
> 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"
>
> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
> http://www.sypron.nl
> Sypron B.V., Amersfoort, The Netherlands
> Chamber of Commerce 27138666
> -----------------------------------------------------------------
>
> "CP.L" <abc@abc.com> wrote in message
> news:4a52f517$2@forums-3-dub.sybase.com...
>> Dear all,
>>
>> I've installed a ase 12.5 database on my PC (window XP), it's tested okay.
>>
>> yesterday, when I run a sql, message display as below :-
>>
>> Can't allocate space for object 'temp worktable' in database 'tempdb'
>> because 'system' 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.
>>
>> Below is the output result after using sp_helpdb command
>>
>> device_fragments size usage created free kbytes
>> mdedat 200.0 MB data only Jun 25 2009 6:04PM
>> 137534
>> mdelog 80.0 MB log only Jun 25 2009 6:04PM not
>> applicable
>>
>>
>> mdedat is the data, mdelog is the log
>>
>> All, can you tell me how to fix it since I'not familiar with ASE database
>> command.
>>
>> Thanks!
>>
>>
>>
>> --
>> Free News Reader
>> http://put.hk
>> http://put.hk/reader/forums.sybase.com/sybase.public.ase.general.html
>>
>>
>>
>
>


CP.L Posted on 2009-07-08 01:43:54.0Z
From: "CP.L" <abc@abc.com>
Newsgroups: sybase.public.ase.general
Subject: Re: ASe database problem
References: <4a52f517$2@forums-3-dub.sybase.com> <4a5301a1@forums-3-dub.sybase.com> <4a531eb0$2@forums-3-dub.sybase.com>
X-Newsreader: newsgroupstats.hk
X-HTTP-Posting-Host: 203.31.32.92
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a53f9da$2@forums-3-dub.sybase.com>
Date: 7 Jul 2009 18:43:54 -0700
X-Trace: forums-3-dub.sybase.com 1247017434 10.22.241.152 (7 Jul 2009 18:43:54 -0700)
X-Original-Trace: 7 Jul 2009 18:43:54 -0700, vip152.sybase.com
Lines: 122
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27968
Article PK: 77218

Dear Mark,

For
1. select getdate()
go

Result is the current date. no strange message display

2. select * from master..syslogshold
go

Output result is null, no strange message display.

3. select * from master..systransactions
go

Output result is null, no strange message display.


Rgds,

CP.L



--
Free News Reader
http://put.hk
http://put.hk/reader/forums.sybase.com/sybase.public.ase.general.html

Mark A. Parsons <iron_horse@no_spamola.compuserve.com> wrote:
> Another possibility is that someone has a long running (ie, 'old') transaction in the tempdb database which is keeping
> the log from being truncated. In this case I'd also want to see the results from the following queries:
> ==================
> select getdate()
> go
> select * from master..syslogshold
> go
> select * from master..systransactions
> go
> ==================
> Rob V [Sybase] wrote:
> > Your 'tempdb' database is full. Tempdb is used as a work area (or scratch
> > pad if you like) for various aspects of processing SQL queries.
> > Tempdb being full is normally caused by one of the following:
> > - the tempdb database is unrealistically small, for example because the
> > out-of-the-box size (3 MB) has never been changed, and this could quickly
> > lead to tempdb filling up, causing the message you've seen.
> > - the query is accessing a large amount of data, causing a large sort
> > operation due to -for example- a group-by construct (that's one of the
> > things a 'temp worktable' is used for)
> > - if none of the above, it could also be that someone else is doing some
> > thing out of the ordinary, and consuming the tempdb space (it's shared
> > between all users), leaving your query with insufficient tempdb space to run
> >
> > Anyway, please run the following and post the result:
> >
> > tempdb..sp_helpdb tempdb
> > go
> >
> >
> >
> > 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"
> >
> > mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
> > http://www.sypron.nl
> > Sypron B.V., Amersfoort, The Netherlands
> > Chamber of Commerce 27138666
> > -----------------------------------------------------------------
> >
> > "CP.L" <abc@abc.com> wrote in message
> > news:4a52f517$2@forums-3-dub.sybase.com...
> >> Dear all,
> >>
> >> I've installed a ase 12.5 database on my PC (window XP), it's tested okay.
> >>
> >> yesterday, when I run a sql, message display as below :-
> >>
> >> Can't allocate space for object 'temp worktable' in database 'tempdb'
> >> because 'system' 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.
> >>
> >> Below is the output result after using sp_helpdb command
> >>
> >> device_fragments size usage created free kbytes
> >> mdedat 200.0 MB data only Jun 25 2009 6:04PM
> >> 137534
> >> mdelog 80.0 MB log only Jun 25 2009 6:04PM not
> >> applicable
> >>
> >>
> >> mdedat is the data, mdelog is the log
> >>
> >> All, can you tell me how to fix it since I'not familiar with ASE database
> >> command.
> >>
> >> Thanks!
> >>
> >>
> >>
> >> --
> >> Free News Reader
> >> http://put.hk
> >> http://put.hk/reader/forums.sybase.com/sybase.public.ase.general.html
> >>
> >>
> >>
> >
> >


"Mark A. Parsons" <iron_horse Posted on 2009-07-08 10:19:44.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: ASe database problem
References: <4a52f517$2@forums-3-dub.sybase.com> <4a5301a1@forums-3-dub.sybase.com> <4a531eb0$2@forums-3-dub.sybase.com> <4a53f9da$2@forums-3-dub.sybase.com>
In-Reply-To: <4a53f9da$2@forums-3-dub.sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090706-0, 07/06/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a5472c0$3@forums-3-dub.sybase.com>
Date: 8 Jul 2009 03:19:44 -0700
X-Trace: forums-3-dub.sybase.com 1247048384 10.22.241.152 (8 Jul 2009 03:19:44 -0700)
X-Original-Trace: 8 Jul 2009 03:19:44 -0700, vip152.sybase.com
Lines: 131
X-Authenticated-User: TeamSybase
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27970
Article PK: 77216

The getdate() was just to use as a reference against any long running transactions that might show up in
syslogshold/systransactions (ie, how long had the transaction(s) been open).

If your space issue has cleared (eg, sp_helpdb seems to show 1.7MB of free space in tempdb) then I wouldn't expect to
see any issues in the syslogshold/systransactions output (ie, you'd want to look at these tables *while* the problem
exists).

CP.L wrote:
> Dear Mark,
>
> For
> 1. select getdate()
> go
>
> Result is the current date. no strange message display
>
> 2. select * from master..syslogshold
> go
>
> Output result is null, no strange message display.
>
> 3. select * from master..systransactions
> go
>
> Output result is null, no strange message display.
>
>
> Rgds,
>
> CP.L
>
>
>
> --
> Free News Reader
> http://put.hk
> http://put.hk/reader/forums.sybase.com/sybase.public.ase.general.html
>
> Mark A. Parsons <iron_horse@no_spamola.compuserve.com> wrote:
>> Another possibility is that someone has a long running (ie, 'old') transaction in the tempdb database which is keeping
>> the log from being truncated. In this case I'd also want to see the results from the following queries:
>> ==================
>> select getdate()
>> go
>> select * from master..syslogshold
>> go
>> select * from master..systransactions
>> go
>> ==================
>> Rob V [Sybase] wrote:
>>> Your 'tempdb' database is full. Tempdb is used as a work area (or scratch
>>> pad if you like) for various aspects of processing SQL queries.
>>> Tempdb being full is normally caused by one of the following:
>>> - the tempdb database is unrealistically small, for example because the
>>> out-of-the-box size (3 MB) has never been changed, and this could quickly
>>> lead to tempdb filling up, causing the message you've seen.
>>> - the query is accessing a large amount of data, causing a large sort
>>> operation due to -for example- a group-by construct (that's one of the
>>> things a 'temp worktable' is used for)
>>> - if none of the above, it could also be that someone else is doing some
>>> thing out of the ordinary, and consuming the tempdb space (it's shared
>>> between all users), leaving your query with insufficient tempdb space to run
>>>
>>> Anyway, please run the following and post the result:
>>>
>>> tempdb..sp_helpdb tempdb
>>> go
>>>
>>>
>>>
>>> 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"
>>>
>>> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
>>> http://www.sypron.nl
>>> Sypron B.V., Amersfoort, The Netherlands
>>> Chamber of Commerce 27138666
>>> -----------------------------------------------------------------
>>>
>>> "CP.L" <abc@abc.com> wrote in message
>>> news:4a52f517$2@forums-3-dub.sybase.com...
>>>> Dear all,
>>>>
>>>> I've installed a ase 12.5 database on my PC (window XP), it's tested okay.
>>>>
>>>> yesterday, when I run a sql, message display as below :-
>>>>
>>>> Can't allocate space for object 'temp worktable' in database 'tempdb'
>>>> because 'system' 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.
>>>>
>>>> Below is the output result after using sp_helpdb command
>>>>
>>>> device_fragments size usage created free kbytes
>>>> mdedat 200.0 MB data only Jun 25 2009 6:04PM
>>>> 137534
>>>> mdelog 80.0 MB log only Jun 25 2009 6:04PM not
>>>> applicable
>>>>
>>>>
>>>> mdedat is the data, mdelog is the log
>>>>
>>>> All, can you tell me how to fix it since I'not familiar with ASE database
>>>> command.
>>>>
>>>> Thanks!
>>>>
>>>>
>>>>
>>>> --
>>>> Free News Reader
>>>> http://put.hk
>>>> http://put.hk/reader/forums.sybase.com/sybase.public.ase.general.html
>>>>
>>>>
>>>>
>>>
>
>


CP.L Posted on 2009-07-08 01:40:49.0Z
From: "CP.L" <abc@abc.com>
Newsgroups: sybase.public.ase.general
Subject: Re: ASe database problem
References: <4a52f517$2@forums-3-dub.sybase.com> <4a5301a1@forums-3-dub.sybase.com>
X-Newsreader: newsgroupstats.hk
X-HTTP-Posting-Host: 203.31.32.92
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a53f921$1@forums-3-dub.sybase.com>
Date: 7 Jul 2009 18:40:49 -0700
X-Trace: forums-3-dub.sybase.com 1247017249 10.22.241.152 (7 Jul 2009 18:40:49 -0700)
X-Original-Trace: 7 Jul 2009 18:40:49 -0700, vip152.sybase.com
Lines: 102
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27967
Article PK: 77214

Dear Rob,

Result as below :-

name db_size owner dbid created status
tempdb 7.0 MB sa 2 Jul 08, 2009 select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data


device_fragments size usage created free kbytes
master 3.0 MB data and log Jun 25 2009 11:00AM 1770
master 1.0 MB log only Jul 7 2009 10:30AM not applicable
master 1.0 MB log only Jul 7 2009 10:30AM not applicable
master 1.0 MB log only Jul 7 2009 10:30AM not applicable
master 1.0 MB log only Jul 7 2009 10:30AM not applicable


log only free kbytes = 5866

device segment
master default
master logsegment
master system

Rgds,

CP.L

--
Free News Reader
http://put.hk
http://put.hk/reader/forums.sybase.com/sybase.public.ase.general.html

Rob V [Sybase] <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote:
> Your 'tempdb' database is full. Tempdb is used as a work area (or scratch
> pad if you like) for various aspects of processing SQL queries.
> Tempdb being full is normally caused by one of the following:
> - the tempdb database is unrealistically small, for example because the
> out-of-the-box size (3 MB) has never been changed, and this could quickly
> lead to tempdb filling up, causing the message you've seen.
> - the query is accessing a large amount of data, causing a large sort
> operation due to -for example- a group-by construct (that's one of the
> things a 'temp worktable' is used for)
> - if none of the above, it could also be that someone else is doing some
> thing out of the ordinary, and consuming the tempdb space (it's shared
> between all users), leaving your query with insufficient tempdb space to run
> Anyway, please run the following and post the result:
> tempdb..sp_helpdb tempdb
> go
> 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"
> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
> http://www.sypron.nl
> Sypron B.V., Amersfoort, The Netherlands
> Chamber of Commerce 27138666
> -----------------------------------------------------------------
> "CP.L" <abc@abc.com> wrote in message
> news:4a52f517$2@forums-3-dub.sybase.com...
> > Dear all,
> >
> > I've installed a ase 12.5 database on my PC (window XP), it's tested okay.
> >
> > yesterday, when I run a sql, message display as below :-
> >
> > Can't allocate space for object 'temp worktable' in database 'tempdb'
> > because 'system' 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.
> >
> > Below is the output result after using sp_helpdb command
> >
> > device_fragments size usage created free kbytes
> > mdedat 200.0 MB data only Jun 25 2009 6:04PM
> > 137534
> > mdelog 80.0 MB log only Jun 25 2009 6:04PM not
> > applicable
> >
> >
> > mdedat is the data, mdelog is the log
> >
> > All, can you tell me how to fix it since I'not familiar with ASE database
> > command.
> >
> > Thanks!
> >
> >
> >
> > --
> > Free News Reader
> > http://put.hk
> > http://put.hk/reader/forums.sybase.com/sybase.public.ase.general.html
> >
> >
> >


"Mark A. Parsons" <iron_horse Posted on 2009-07-08 10:34:35.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: ASe database problem
References: <4a52f517$2@forums-3-dub.sybase.com> <4a5301a1@forums-3-dub.sybase.com> <4a53f921$1@forums-3-dub.sybase.com>
In-Reply-To: <4a53f921$1@forums-3-dub.sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090706-0, 07/06/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a54763b@forums-3-dub.sybase.com>
Date: 8 Jul 2009 03:34:35 -0700
X-Trace: forums-3-dub.sybase.com 1247049275 10.22.241.152 (8 Jul 2009 03:34:35 -0700)
X-Original-Trace: 8 Jul 2009 03:34:35 -0700, vip152.sybase.com
Lines: 131
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27972
Article PK: 77219

Your sp_helpdb output looks 'ok' ... kinda.

1 - The 1770 KB of free space would tend to indicate that you have some room in the system (and default) segments.
However, 1770 KB can be used up in-the-blink-of-an-eye by any queries processing even just a few 1000 records.

2 - 7MB is usually (way) too small for the tempdb database in most dataservers.

At this point I'd guesstimate that you received the error message (out of space in system segment) due to your tempdb
database being too small for processing requirements.

I'd suggest you add some space to the tempdb database. I'd probably look at adding a new device and then extending the
tempdb database onto said device. Without knowing what kind of processing you're doing, or the volumes of data being
processed, I'd start out by adding 200MB of space to tempdb.

------------

Properly sizing your tempdb will require some monitoring and analysis on your part.

One option would be to keep adding space each time you get an 'out of space' message.

Another option may be to add multiple thresholds to the tempdb database; have the thresholds print informational
messages to the dataserver errorlog; from these messages you can get an idea of how much space is used during various
times of the day. [NOTE: Thresholds in tempdb will disappear when the dataserver is bounced so you'll need to have a
post-startup process that recreates the thresholds once the dataserver is (re)started.]

Another option would consist of analyzing all of the queries submitted against your dataserver; granted, this is a bit
more complicated than it sounds ... so you may want to stick with one of the other options.

CP.L wrote:
> Dear Rob,
>
> Result as below :-
>
> name db_size owner dbid created status
> tempdb 7.0 MB sa 2 Jul 08, 2009 select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data
>
>
> device_fragments size usage created free kbytes
> master 3.0 MB data and log Jun 25 2009 11:00AM 1770
> master 1.0 MB log only Jul 7 2009 10:30AM not applicable
> master 1.0 MB log only Jul 7 2009 10:30AM not applicable
> master 1.0 MB log only Jul 7 2009 10:30AM not applicable
> master 1.0 MB log only Jul 7 2009 10:30AM not applicable
>
>
> log only free kbytes = 5866
>
> device segment
> master default
> master logsegment
> master system
>
> Rgds,
>
> CP.L
>
> --
> Free News Reader
> http://put.hk
> http://put.hk/reader/forums.sybase.com/sybase.public.ase.general.html
>
> Rob V [Sybase] <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote:
>> Your 'tempdb' database is full. Tempdb is used as a work area (or scratch
>> pad if you like) for various aspects of processing SQL queries.
>> Tempdb being full is normally caused by one of the following:
>> - the tempdb database is unrealistically small, for example because the
>> out-of-the-box size (3 MB) has never been changed, and this could quickly
>> lead to tempdb filling up, causing the message you've seen.
>> - the query is accessing a large amount of data, causing a large sort
>> operation due to -for example- a group-by construct (that's one of the
>> things a 'temp worktable' is used for)
>> - if none of the above, it could also be that someone else is doing some
>> thing out of the ordinary, and consuming the tempdb space (it's shared
>> between all users), leaving your query with insufficient tempdb space to run
>> Anyway, please run the following and post the result:
>> tempdb..sp_helpdb tempdb
>> go
>> 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"
>> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
>> http://www.sypron.nl
>> Sypron B.V., Amersfoort, The Netherlands
>> Chamber of Commerce 27138666
>> -----------------------------------------------------------------
>> "CP.L" <abc@abc.com> wrote in message
>> news:4a52f517$2@forums-3-dub.sybase.com...
>>> Dear all,
>>>
>>> I've installed a ase 12.5 database on my PC (window XP), it's tested okay.
>>>
>>> yesterday, when I run a sql, message display as below :-
>>>
>>> Can't allocate space for object 'temp worktable' in database 'tempdb'
>>> because 'system' 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.
>>>
>>> Below is the output result after using sp_helpdb command
>>>
>>> device_fragments size usage created free kbytes
>>> mdedat 200.0 MB data only Jun 25 2009 6:04PM
>>> 137534
>>> mdelog 80.0 MB log only Jun 25 2009 6:04PM not
>>> applicable
>>>
>>>
>>> mdedat is the data, mdelog is the log
>>>
>>> All, can you tell me how to fix it since I'not familiar with ASE database
>>> command.
>>>
>>> Thanks!
>>>
>>>
>>>
>>> --
>>> Free News Reader
>>> http://put.hk
>>> http://put.hk/reader/forums.sybase.com/sybase.public.ase.general.html
>>>
>>>
>>>
>
>


CP.L Posted on 2009-07-09 02:34:22.0Z
From: "CP.L" <abc@abc.com>
Newsgroups: sybase.public.ase.general
Subject: Re: ASe database problem
References: <4a52f517$2@forums-3-dub.sybase.com> <4a5301a1@forums-3-dub.sybase.com> <4a53f921$1@forums-3-dub.sybase.com> <4a54763b@forums-3-dub.sybase.com>
X-Newsreader: newsgroupstats.hk
X-HTTP-Posting-Host: 203.31.32.92
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a55572e@forums-3-dub.sybase.com>
Date: 8 Jul 2009 19:34:22 -0700
X-Trace: forums-3-dub.sybase.com 1247106862 10.22.241.152 (8 Jul 2009 19:34:22 -0700)
X-Original-Trace: 8 Jul 2009 19:34:22 -0700, vip152.sybase.com
Lines: 134
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27981
Article PK: 77227

I'm not familiar with ASE command, how to add more space to tempdb.

Thansk!


--
Free News Reader
http://put.hk
http://put.hk/reader/forums.sybase.com/sybase.public.ase.general.html

Mark A. Parsons <iron_horse@no_spamola.compuserve.com> wrote:
> Your sp_helpdb output looks 'ok' ... kinda.
> 1 - The 1770 KB of free space would tend to indicate that you have some room in the system (and default) segments.
> However, 1770 KB can be used up in-the-blink-of-an-eye by any queries processing even just a few 1000 records.
> 2 - 7MB is usually (way) too small for the tempdb database in most dataservers.
> At this point I'd guesstimate that you received the error message (out of space in system segment) due to your tempdb
> database being too small for processing requirements.
> I'd suggest you add some space to the tempdb database. I'd probably look at adding a new device and then extending the
> tempdb database onto said device. Without knowing what kind of processing you're doing, or the volumes of data being
> processed, I'd start out by adding 200MB of space to tempdb.
> ------------
> Properly sizing your tempdb will require some monitoring and analysis on your part.
> One option would be to keep adding space each time you get an 'out of space' message.
> Another option may be to add multiple thresholds to the tempdb database; have the thresholds print informational
> messages to the dataserver errorlog; from these messages you can get an idea of how much space is used during various
> times of the day. [NOTE: Thresholds in tempdb will disappear when the dataserver is bounced so you'll need to have a
> post-startup process that recreates the thresholds once the dataserver is (re)started.]
> Another option would consist of analyzing all of the queries submitted against your dataserver; granted, this is a bit
> more complicated than it sounds ... so you may want to stick with one of the other options.
> CP.L wrote:
> > Dear Rob,
> >
> > Result as below :-
> >
> > name db_size owner dbid created status
> > tempdb 7.0 MB sa 2 Jul 08, 2009 select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data
> >
> >
> > device_fragments size usage created free kbytes
> > master 3.0 MB data and log Jun 25 2009 11:00AM 1770
> > master 1.0 MB log only Jul 7 2009 10:30AM not applicable
> > master 1.0 MB log only Jul 7 2009 10:30AM not applicable
> > master 1.0 MB log only Jul 7 2009 10:30AM not applicable
> > master 1.0 MB log only Jul 7 2009 10:30AM not applicable
> >
> >
> > log only free kbytes = 5866
> >
> > device segment
> > master default
> > master logsegment
> > master system
> >
> > Rgds,
> >
> > CP.L
> >
> > --
> > Free News Reader
> > http://put.hk
> > http://put.hk/reader/forums.sybase.com/sybase.public.ase.general.html
> >
> > Rob V [Sybase] <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote:
> >> Your 'tempdb' database is full. Tempdb is used as a work area (or scratch
> >> pad if you like) for various aspects of processing SQL queries.
> >> Tempdb being full is normally caused by one of the following:
> >> - the tempdb database is unrealistically small, for example because the
> >> out-of-the-box size (3 MB) has never been changed, and this could quickly
> >> lead to tempdb filling up, causing the message you've seen.
> >> - the query is accessing a large amount of data, causing a large sort
> >> operation due to -for example- a group-by construct (that's one of the
> >> things a 'temp worktable' is used for)
> >> - if none of the above, it could also be that someone else is doing some
> >> thing out of the ordinary, and consuming the tempdb space (it's shared
> >> between all users), leaving your query with insufficient tempdb space to run
> >> Anyway, please run the following and post the result:
> >> tempdb..sp_helpdb tempdb
> >> go
> >> 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"
> >> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
> >> http://www.sypron.nl
> >> Sypron B.V., Amersfoort, The Netherlands
> >> Chamber of Commerce 27138666
> >> -----------------------------------------------------------------
> >> "CP.L" <abc@abc.com> wrote in message
> >> news:4a52f517$2@forums-3-dub.sybase.com...
> >>> Dear all,
> >>>
> >>> I've installed a ase 12.5 database on my PC (window XP), it's tested okay.
> >>>
> >>> yesterday, when I run a sql, message display as below :-
> >>>
> >>> Can't allocate space for object 'temp worktable' in database 'tempdb'
> >>> because 'system' 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.
> >>>
> >>> Below is the output result after using sp_helpdb command
> >>>
> >>> device_fragments size usage created free kbytes
> >>> mdedat 200.0 MB data only Jun 25 2009 6:04PM
> >>> 137534
> >>> mdelog 80.0 MB log only Jun 25 2009 6:04PM not
> >>> applicable
> >>>
> >>>
> >>> mdedat is the data, mdelog is the log
> >>>
> >>> All, can you tell me how to fix it since I'not familiar with ASE database
> >>> command.
> >>>
> >>> Thanks!
> >>>
> >>>
> >>>
> >>> --
> >>> Free News Reader
> >>> http://put.hk
> >>> http://put.hk/reader/forums.sybase.com/sybase.public.ase.general.html
> >>>
> >>>
> >>>
> >
> >


"Mark A. Parsons" <iron_horse Posted on 2009-07-09 06:17:22.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: ASe database problem
References: <4a52f517$2@forums-3-dub.sybase.com> <4a5301a1@forums-3-dub.sybase.com> <4a53f921$1@forums-3-dub.sybase.com> <4a54763b@forums-3-dub.sybase.com> <4a55572e@forums-3-dub.sybase.com>
In-Reply-To: <4a55572e@forums-3-dub.sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 090708-0, 07/08/2009), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a558b72$1@forums-3-dub.sybase.com>
Date: 8 Jul 2009 23:17:22 -0700
X-Trace: forums-3-dub.sybase.com 1247120242 10.22.241.152 (8 Jul 2009 23:17:22 -0700)
X-Original-Trace: 8 Jul 2009 23:17:22 -0700, vip152.sybase.com
Lines: 139
X-Authenticated-User: TeamSybase
Path: forums-1-dub!forums-master!forums-3-dub.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:27982
Article PK: 77229

To create a new device you'll need to use the 'disk init' command.

Once the device(s) has been created you will use the 'alter database' command to extend the tempdb database onto the new
device(s).

CP.L wrote:
> I'm not familiar with ASE command, how to add more space to tempdb.
>
> Thansk!
>
>
> --
> Free News Reader
> http://put.hk
> http://put.hk/reader/forums.sybase.com/sybase.public.ase.general.html
>
> Mark A. Parsons <iron_horse@no_spamola.compuserve.com> wrote:
>> Your sp_helpdb output looks 'ok' ... kinda.
>> 1 - The 1770 KB of free space would tend to indicate that you have some room in the system (and default) segments.
>> However, 1770 KB can be used up in-the-blink-of-an-eye by any queries processing even just a few 1000 records.
>> 2 - 7MB is usually (way) too small for the tempdb database in most dataservers.
>> At this point I'd guesstimate that you received the error message (out of space in system segment) due to your tempdb
>> database being too small for processing requirements.
>> I'd suggest you add some space to the tempdb database. I'd probably look at adding a new device and then extending the
>> tempdb database onto said device. Without knowing what kind of processing you're doing, or the volumes of data being
>> processed, I'd start out by adding 200MB of space to tempdb.
>> ------------
>> Properly sizing your tempdb will require some monitoring and analysis on your part.
>> One option would be to keep adding space each time you get an 'out of space' message.
>> Another option may be to add multiple thresholds to the tempdb database; have the thresholds print informational
>> messages to the dataserver errorlog; from these messages you can get an idea of how much space is used during various
>> times of the day. [NOTE: Thresholds in tempdb will disappear when the dataserver is bounced so you'll need to have a
>> post-startup process that recreates the thresholds once the dataserver is (re)started.]
>> Another option would consist of analyzing all of the queries submitted against your dataserver; granted, this is a bit
>> more complicated than it sounds ... so you may want to stick with one of the other options.
>> CP.L wrote:
>>> Dear Rob,
>>>
>>> Result as below :-
>>>
>>> name db_size owner dbid created status
>>> tempdb 7.0 MB sa 2 Jul 08, 2009 select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data
>>>
>>>
>>> device_fragments size usage created free kbytes
>>> master 3.0 MB data and log Jun 25 2009 11:00AM 1770
>>> master 1.0 MB log only Jul 7 2009 10:30AM not applicable
>>> master 1.0 MB log only Jul 7 2009 10:30AM not applicable
>>> master 1.0 MB log only Jul 7 2009 10:30AM not applicable
>>> master 1.0 MB log only Jul 7 2009 10:30AM not applicable
>>>
>>>
>>> log only free kbytes = 5866
>>>
>>> device segment
>>> master default
>>> master logsegment
>>> master system
>>>
>>> Rgds,
>>>
>>> CP.L
>>>
>>> --
>>> Free News Reader
>>> http://put.hk
>>> http://put.hk/reader/forums.sybase.com/sybase.public.ase.general.html
>>>
>>> Rob V [Sybase] <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote:
>>>> Your 'tempdb' database is full. Tempdb is used as a work area (or scratch
>>>> pad if you like) for various aspects of processing SQL queries.
>>>> Tempdb being full is normally caused by one of the following:
>>>> - the tempdb database is unrealistically small, for example because the
>>>> out-of-the-box size (3 MB) has never been changed, and this could quickly
>>>> lead to tempdb filling up, causing the message you've seen.
>>>> - the query is accessing a large amount of data, causing a large sort
>>>> operation due to -for example- a group-by construct (that's one of the
>>>> things a 'temp worktable' is used for)
>>>> - if none of the above, it could also be that someone else is doing some
>>>> thing out of the ordinary, and consuming the tempdb space (it's shared
>>>> between all users), leaving your query with insufficient tempdb space to run
>>>> Anyway, please run the following and post the result:
>>>> tempdb..sp_helpdb tempdb
>>>> go
>>>> 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"
>>>> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
>>>> http://www.sypron.nl
>>>> Sypron B.V., Amersfoort, The Netherlands
>>>> Chamber of Commerce 27138666
>>>> -----------------------------------------------------------------
>>>> "CP.L" <abc@abc.com> wrote in message
>>>> news:4a52f517$2@forums-3-dub.sybase.com...
>>>>> Dear all,
>>>>>
>>>>> I've installed a ase 12.5 database on my PC (window XP), it's tested okay.
>>>>>
>>>>> yesterday, when I run a sql, message display as below :-
>>>>>
>>>>> Can't allocate space for object 'temp worktable' in database 'tempdb'
>>>>> because 'system' 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.
>>>>>
>>>>> Below is the output result after using sp_helpdb command
>>>>>
>>>>> device_fragments size usage created free kbytes
>>>>> mdedat 200.0 MB data only Jun 25 2009 6:04PM
>>>>> 137534
>>>>> mdelog 80.0 MB log only Jun 25 2009 6:04PM not
>>>>> applicable
>>>>>
>>>>>
>>>>> mdedat is the data, mdelog is the log
>>>>>
>>>>> All, can you tell me how to fix it since I'not familiar with ASE database
>>>>> command.
>>>>>
>>>>> Thanks!
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Free News Reader
>>>>> http://put.hk
>>>>> http://put.hk/reader/forums.sybase.com/sybase.public.ase.general.html
>>>>>
>>>>>
>>>>>
>>>
>
>