Skip to Content
avatar image
Former Member

ASE SQL ERROR 1105

Dear Gurus,

Good afternoon. I am facing the below error in our ecc production server. I am pasting the sid.log below for your understanding.

00:0008:00000:00112:2015/10/30 11:32:17.31 server  Error: 1105, Severity: 17, State: 2
00:0008:00000:00112:2015/10/30 11:32:17.31 server  Can't allocate space for object 'temp worktable' in database 'saptempdb' 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.
00:0008:00000:00112:2015/10/30 11:34:41.74 server  Error: 1105, Severity: 17, State: 2
00:0008:00000:00112:2015/10/30 11:34:41.74 server  Can't allocate space for object 'temp worktable' in database 'saptempdb' 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.


our database is on sybase and os is on suse linux. i added space to database using isql editor with the below command but still issue is persisting.

after login to isql editor i used the below command and added space to databaes


use master

go

disk resize name="SID_data_001{,size='XGB'

go

alter database SID on SID_data_001='XGB'

go


here in the above log its showing i need to add some space on saptempdb..i am new to sybase so please guide what should i do and what command should i need to perform and add the space and how much space should i need to add to saptempdb. I am waiting for your valuable feedback and suggestions. i am attaching database log and also the sm21 log for your reference.Thanks in advance.


Best regards,

venkat.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • avatar image
    Former Member
    Oct 30, 2015 at 07:00 AM

    please guide me gurus its very urgent in production server users are running this report regulary actually its a customized tcode "ZGRR" when they are running its throwing the dump for some users. i need to increase space in saptempdb but i dont know the exact command just i increase the normal space to database.

    BR,

    venkat

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 30, 2015 at 07:28 AM

    Hi Venkat,

    Run the following command:

    1> sp_helpdb tempdb

    2> go

    name                     db_size

             owner                dbid

             created

             durability

             lobcomplvl

             inrowlen

             status

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

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

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

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

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

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

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

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

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

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

    tempdb                       1048.0 MB

             sa                      2

             Oct 23, 2015

             no_recovery

                      0

                 NULL

             select into/bulkcopy/pllsort, trunc log on chkpt, ddl in tran, allow nu

             lls by default, abort tran on log full, mixed log and data, allow wide

             dol rows

    (1 row affected)

    device_fragments               size          usage

             created                   free kbytes

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

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

    master                               24.0 MB data only

             Sep 10 2014 10:36AM                  24480

    tempdbdev                          1024.0 MB data and log

             Sep 10 2014 10:39AM                1043632

    (return status = 0)

    Here you can see your device is tempdbdev and master.

    Take note master has data only and tempdbdev has data and log. Therefore you want to allocate data and log to tempdbdev.

    If for example you run this.

    use master

    go

    disk resize name ='tempdbdev' , size='1G'

    go

    alter database tempdb log on tempdbdev = '500M'

    go

    alter database tempdb on tempdbdev = '500M'

    go


    This means you increased the tempdb device by 1G in size. And of that you allocated 500MB to data and 500MB to log.


    Just bare the distinction of the commands above.


    Kind Regards,


    Johan

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 30, 2015 at 08:40 AM

    Hi,

    Whilst awaiting your command output you can refer to this for example.

    1752266 - SYB: How to create an additional tempdb with Sybase ASE

    They dont mention a specific size there. But since your already have a saptempdb this is just good information to know.

    You can also refer to 2232437

    Check both of these after your issue is resolved.

    Johan

    Add comment
    10|10000 characters needed characters exceeded