Msg1105 Level 17 State 16: in database 'NPL' , 'default' segment is full/has no free extents

[stephen_xue]

Hi,

i have got lots of dump in ST22 as below

i have tried to reorg the table and got the following error in isql

however when i check the "default" segment of NPL, i found there are still lots of free pages:

any ideas what the problem is? my system information is:

NW ABAP 7.52 SP04 Developer Edition.

Regards

[rafael_cano_perez · Accepted solution]

Hi Stephen

This are some commands I use to monitor and extend the database ( in centos )

isql64 is started from the terminal as user sybnpl, the db admin user. You should have the passwords as used when you installed.

The rest is run inside isql64

[sybnpl]$ isql64 -X -Usapsa -SNPL -Pwhateveryourpassword -w999 ( for output command width )To clear logsegment use the following commands,use master
go
dump transaction tempdb with no_log
dump transaction sybsystemdb with no_log
dump transaction sybsystemprocs with no_log
dump transaction sybmgmtdb with no_log
dump transaction saptools with no_log
dump transaction saptempdb with no_log
dump transaction model with no_log
dump transaction NPL with no_loggo
exitShow database spacesp_helpdb NPL ( also for master saptempdb saptools tempdb )
go

use NPL
sp_spaceused
go

database_name database_size
---------------------------------------------------- ----------------------------------------------------
NPL 48128.0 MBNPL 64512.0 MB

(1 row affected)
reserved data index_size unused
--------------------- --------------------- -------------------- ----------------------------------------
39684464 KB 17033280 KB 17749472 KB 4509376 KB


database_name database_size
---------------------------------------------------- ----------------------------------------------------
NPL 64512.0 MB NPL 48128.0 MBuse NPL
go
sp_helpdevice
goShow one fileuse NPL
go
sp_helpdevice NPL_data_001
go

... 38912.00 MB, Free: 0.00 MB Expand one fileuse master
go
disk resize name="NPL_data_001", size="16384M"
go
alter database NPL on NPL_data_001="16384M"
goExtending database by 1048576 pages (16384.0 megabytes) on disk NPL_data_001
Processed 410 allocation unit(s) out of 4096 units (allocation page 2595072). 10% completed.
...
Processed 4096 allocation unit(s) out of 4096 units (allocation page 4194048). 100% completed.use master
disk resize name="NPL_log_001", size="8192M"
go
alter database NPL log on NPL_log_001="8192M"
gosp_helpdb NPL
goDisable transaccional logssp_helpdb
go

use master
go
sp_dboption NPL, 'trunc log on chkpt',true
go

Database option 'trunc log on chkpt' turned ON for database 'NPL'.
Running CHECKPOINT on database 'NPL' for option 'trunc log on chkpt' to take
effect.
(return status = 0)
checkpoint all
go

Hope it gives you some clues.

Regards, Rafael

[stephen_xue · Accepted solution]

Thanks Rafael. i did learn something important from your reply.

however when 5G has been added to NPL_log_001, the free space is still 0 which makes me quite confused.

I managed to clear up all logs by using tcode SLG2 and it looks solved my issue at the moment. i haven't got any dumps like that.