on 10-30-2015 6:49 AM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
94 | |
11 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.