ASE SQL ERROR 1105

[former_member230979]

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.

[Johan_sapbasis]

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

[Johan_sapbasis]

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

[former_member230979]

Dear Nicolas,

Thank you for your reply. How much space should in increase nicolas. Is 1 gb is enough or else i need to add 2gb. how should i know how much space can i add to tempdb device. please guide me regarding.I will provide technical inputs if you need anything from my side. Thanks in advance.

Best regards,

venkat.

[Johan_sapbasis]

hi,

what is the output of the following command on your system:

sp_helpdb saptempdb

I want to see this of your system:

1> sp_helpdb saptempdb

2> go

name

         db_size

         owner                dbid

         created

         durability

         lobcomplvl

         inrowlen

         status

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

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

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

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

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

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

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

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

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

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

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

saptempdb

             2048.0 MB

         sa                      6

         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, user create

         d temp db, allow wide dol rows

(1 row affected)

device_fragments               size          usage

         created                   free kbytes

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

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

saptempdb_data_001                 2048.0 MB data and log

         Sep 10 2014 10:52AM                2075328

(return status = 0)

Regards,

Johan

[former_member230979]

Dear nicolas,

which command should i run little bit confusion.

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

OR AS MENTIONED BY STEPHEN

use master

go

disk resize name=saptempdb_data_001 ,size='XGB'

go

alter database saptempdb on saptempdb_data_001='XGB'

go

please give clarity regarding on this Mr.nicolas.

Best regards,

venkat

[Johan_sapbasis]

Hi,

Run only this now and give me output.

sp_helpdb saptempdb

Johan

[former_member230979]

here is the output for your reference nicolas

[Johan_sapbasis]

Hi,

Now run these commands.

You are extending the device by 3GB and adding 2GB to DB and 1GB to log.

use master

go

disk resize name ='saptempdb_data_001' , size='3G'

go

alter database saptempdb log on saptempdb_data_001 = '1G'

go

alter database saptempdb on saptempdb_data_001 = '2G'

go

Kind Regards,

Johan

[Johan_sapbasis]

Hi Venkata,

How is it looking now?

Kind Regards,

Johan

[former_member230979]

Dear nicolas,

Thank you for your reply. I am new to sybase that's why i am hesitating to do changes on my own in our production database. As per your suggestion i am going to run these commads in our production.

Best regards,

venkat.

[former_member230979]

Dear nicolas,

I have to run and will send the output once it is over. So no problem to our production database  is there right with the implementation of this commands

use master

go

disk resize name ='saptempdb_data_001' , size='3G'

go

alter database saptempdb log on saptempdb_data_001 = '1G'

go

alter database saptempdb on saptempdb_data_001 = '2G'

go

Best regards,

venkat

[Johan_sapbasis]

Hi Venkata,

Yes please do, it will only add size.

The only consideration is to make sure that on the drive where your database is has enough free space to grow by the 3gb of course.

It wont break anything.

Johan

[Johan_sapbasis]

HI,

Your issue should be resolved please close this thread.

J

[former_member230979]

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

[Former Member]

Dear Venkat,

if you want to resize the 'saptempdb' you have to specify the device of saptempdb and the database saptempdb.

It could be like this:

use master

go

disk resize name=saptempdb_data_001 ,size='XGB'

go

alter database saptempdb on saptempdb_data_001='XGB'

go

The commands 'sp_helpdevice' and 'sp_helpdb' shows you the needed information about devices and databases...

Regarding the size of saptempdb it depends on your daily doing...there could be SAP installations with nearly 200GB, 2GB could also be enough.

Best regards,

Stephan

[former_member230979]

Dear stephan,

Thank you for your reply. Could i proceed with the below commands.

Best regards,

venkat.

[Johan_sapbasis]

HI,

Just run the commands for your saptempdb. and not tempdb as I did.

But yes the commands are valid.

Johan