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.

[rafael_cano_perez]

Hi Stephen

The formatting of my answer came really bad ( these HTML editors .... )

My experience is that when I expand the transactional log with something like:

use master 
disk resize name="NPL_log_001", size="8192M" 
go 
alter database NPL log on NPL_log_001="8192M"            
go 


use NPL 
go 
sp_helpdevice NPL_log_001
go

sp_helpdb NPL
go 

I get to see that the log has some new free space.

Anyway, it is good that you don't get spade dumps anymore.

Regards, Rafael

[stephen_xue]

is the free space you see same as the amount added, which is 8G here? i have tried to add 50M to a device which is full and the free disc added was around 2.5M something when the device has been checked, which is really wired.

the deleting of application log in the ABAP stack does help so far. 🙂 thx

[stephane_g]

The solution is here

https://blogs.sap.com/2019/12/22/resize-ase-database-in-npl-7.52-sp-04/

[stephen_xue]

thx, i will try it when i have time. lol

[julieplummer20]

Hi Stephane, Thanks a lot for your detective work. I am sorry you have been having problems.

I have passed on your comments, re the "hole" to the relevant team.

Best wishes,
Julie.

[stephane_g]

Hi Stephen, did you eventually find a fix for this issue?

I am facing exactly the same problem. I have increased the size of NPL_data_001 by 5G but still the same dump when I try 'reorg rebuild NPL.SAPSR3.D010INC' (or NPL.SAPSR3.D010TAB).

I looks as if the free space allocated via 'disk resize' and 'alter database' is not accessible ???

However when making free data space by clearing logs via SLG2 (in client 001 and 000) it seems to work better. But as I have run SGEN afterwards that free space was quickly filled. And the 5G previously allocated are still free ;-(

[stephen_xue]

unfortunately i have not. one of the walk around is to delete all of logs in ABAP stack via tcode SLG2.

i haven't tried the new solution proposed by Stephane Gabric below. you can have time try it. thanks

[Sriram2009]

Hi Stephen.

Refer the SAP Note 1836607 for the error message"Msg1105 Level 17 State 16: in database 'NPL' , 'default' segment is full/has no free extents"

Regards

Sriram

[stephen_xue]

Thanks Sriram

i have read that note. however my disk or database is not full. see the view below:

NPL has nearly 7G free space after extending the initial space from around 2G. assume the issue is not that straightforward. thanks