ASe database problem

[CP.L]

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]]

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:r...@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------

"CP.L" <a...@abc.com> wrote in message
news:4a52f517$2...@forums-3-dub.sybase.com...

[Mark A. Parsons]

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
==================

[CP.L]

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

[CP.L]

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

[Mark A. Parsons]

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).

[Mark A. Parsons]

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]

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]

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).