Skip to Content
Technical Articles
Author's profile photo Paweł Wiejkut

Resize ASE database in NPL 7.52 SP 04

By default, NPL is shipped with database size ~25GB, and ~2GB free storage but what if this space will end? Today I will show you a fast solution for resizing the database in NPL 7.52 SP 04. Why for this particular version? In older versions, the only thing you have to do is simply execute disk resize and alter the database, but this particular is affected by a small issue.

Issue explanation

This tutorial can cause an issue on your database, which can be solved by ‘reorg rebuild’ command. Please see the side effects section.

If you just resize this database you probably get in any case a message:

[ASE Error SQL1105]Can't allocate space for object 'XYZ' in database 'NPL' because 'default' 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.

This is probably caused by known ASE limitations. More information is available here. And in notes:

If you download and execute a script attached to 2822555, you will see a similar error:

Basically, between rows 1614592 and 1835008 is a hole, which causes our problem. To avoid this we have to patch it.

Prepare system

Lets login to the sybnpl, and increase ‘max memory’ parameter first:

su -l sybnpl
isql -Usapsa -X -SNPL

Note about memory configuration is available here.

sp_configure 'max memory',736621
go

Now let’s allow updates on system tables

isql -Usapsso -X -SNPL sp_configure "allow updates to system tables", 1 go

Patch the hole

If the database is not resized yet, we can perform now this operation. In my case, I increase DB space at 5 GB up to ~30 GB

isql -Usapsa -X -SNPL 
disk resize name="NPL_data_001",size = '5G'  
alter database NPL on NPL_data_001 = '5G'

Now it’s time to the most important line

insert into sysusages values (5,0,1623808,211200,1623808,10,1623808,"Dec 19 2019  8:21AM",-5)

After all, it is also a good idea to run

dbcc dbrepair(NPL,remap) 
go
dbcc dbrepair(NPL, ltmignore)
go

Now your DB should be properly resized.

Side effects

It is possible that someday you get a dump like:

SQL error 5702 while accessing program " part "LREF".

"[ASE Error SQL21][SAP][ASE ODBC Driver][Adaptive Server
Enterprise]WARNING - Fatal Error 2628 occurred at Dec 22 2019  4:16PM.
Please note the error and time, and contact a user with System Administrator (SA) authorization.#"

If you check your sm21, there should be a message error with the table name, like:

[ASE Error SQL21][SAP][ASE ODBC Driver][Adaptive Server       79
WARNING - Fatal Error 2628 occurred at Dec 22 2019 79
Dtabase error 5702 with INS access to table D010TAB

If you get an error like this, you should run reorg on the table mentioned in the message, i.e.

reorg rebuild NPL.SAPSR3.D010TAB

Assigned Tags

      19 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Sandra Rossi
      Sandra Rossi

      EDIT March 23rd, 2019:

      Author's profile photo Paweł Wiejkut
      Paweł Wiejkut
      Blog Post Author

      Shure resizing is simple, but not in the case of 7.52 SP 04. The article mentioned by you does not covering the "hole issue" , because this issue doesn't exist in previous versions.

      Author's profile photo Alban Leong
      Alban Leong

      Hi Sandra Rossi, yes – I have installed the latest ABAP 7.52 SP04 and added the additional space NOT through a resize like described in this blog post, but extending by giving it an additional disk for storage as describe in my earlier post in 2017.

      I can confirm it still works (see screenshots below).

      Author's profile photo Paweł Wiejkut
      Paweł Wiejkut
      Blog Post Author

      Try fill this database to max. Issue starts when you exceed initial storage or just try to run script attached to 2822555, this is the simplest way to confirm that this issue exists.

      Author's profile photo stephen xue
      stephen xue

      Hi Pawet,

      with regard to your command

      insert into sysusages values (5,0,1623808,211200,1623808,10,1623808,"Dec 19 2019  8:21AM",-5)

      should i change the date time to the current datetime or not? for those values used in the first 7 fields, are they fixed or should i refer to a specific report of my system?

       

      thanks

      Author's profile photo Paweł Wiejkut
      Paweł Wiejkut
      Blog Post Author

      Hi Stephen,

      I don't think that this is important, but please check and share.

      Author's profile photo Alban Leong
      Alban Leong

      Hi Paweł Wiejkut ,

      I finally found some time to try and max out the DB and test this out and yes, I can confirm that even if I had added more space to the DB but due to this "hole" issue as you've pointed out, I am not able to utilize that space. Thanks for the blog!

      Sandra Rossi - just an FYI.

       

      Author's profile photo Stephane Gabric
      Stephane Gabric

      Brilliant, thanks Pawel !!!

      Author's profile photo MARIA ALEJANDRA DANTUR
      MARIA ALEJANDRA DANTUR

      Hi Pawet, so grateful for the blog. It is kind of saving my life after a not very happy SGEN that filled databases.

      I am not being able to execute the

      insert into sysusages values (5,0,1623808,211200,1623808,10,1623808,"Dec 19 2019  8:21AM",-5)

      I am getting the message sysusages not found. Specify owner.objectname or use sp_help etc etc.

      I am logged to isql as Usapsa, to the operating system as sybnpl. Tried to check ASE manuals for help but cant find any, Actually I am not sure who owns sysusages table. Thanks!!!!

       

      Author's profile photo Stephane Gabric
      Stephane Gabric

      Hi Maria, I have managed to implement the solution that Pawel describes, but with some small adjustments, adding ‘use master’ and ‘exit’ when changing db user.

      Try this :

      -> su -l sysbnpl

      pw: xxxxxxx

      > isql -Usapsa -X -SNPL

      > use master

      > go

      > sp_configure ‘max memory’, 736621

      > go

      (1 row affected) … etc

      (return status = 0)

      > exit

      % isql -Usapsso -X -SNPL

      pw: xxxxxxxx

      > sp_configure “allow updates to system tables”, 1

      > go

      (1 row affected) … etc

      (return code = 0)

      > exit

      % isql -Usapsa -X -SNPL

      pw: xxxxx

      > use master

      > go

      > disk resize name = “NPL_data_001”, size = ’10G’ 

      > go

      > alter database NPL on NPL_data_001 = ’10G’

      > go 

      > insert into sysusages values (5,0,1623808,211200,1623808,10,1623808,”Dec 19 2019  8:21AM”,-5)

      > go

      (1 row affected)

      > dbcc dbrepair(NPL,remap)

      > go

      > dbcc dbrepair(NPL, ltmignore)

      >go

      + if you had this error when running SGEN prior to extend the db space

      Runtime error "DBIF_DSQL2_SQL_ERROR" occurred.
      > Short dump "200501 205522 vhcalnplci_NPL_00 DEVELOPER" created.
      Database error 1105 with INS access to table D010INC
      > [ASE Error SQL1105][SAP][ASE ODBC Driver][Adaptive Server
      > Enterprise]Can't allocate space for object 'D010INC' in
      > database 'NPL' … etc  … then also do

      >reorg rebuild NPL.SAPSR3.D010TAB
      >go
      >reorg rebuild NPL.SAPSR3.D010INC
      >go

       

      After that the SGEN job should run without any issue if you have added enough free space (>= 10GB) to NPL_data_001.

       

      Stephane

       

       

       

      Author's profile photo Paweł Wiejkut
      Paweł Wiejkut
      Blog Post Author

      Thank you for sharing !

      Author's profile photo MARIA ALEJANDRA DANTUR
      MARIA ALEJANDRA DANTUR

      Thanks Stephane!! This worked nicely! and good to keep in mind for other isql commands!!!!

      Regards

      Maria Alejandra

      Author's profile photo Ahmad Hussain
      Ahmad Hussain

      Hi Pawet and Stephane,

      I have done the above and it resolved my issue thanks for that, if i need more space again say 5 GB
      Values will change only in these statements

      disk resize name = “NPL_data_001”, size = ’XG’

      alter database NPL on NPL_data_001 = ’XG’

      Everything else will remain the same??

      Author's profile photo Vivek Ashok Pai
      Vivek Ashok Pai

      Hi Pawel,

      Thanks for the fix, it solved the issue I was facing.

      Author's profile photo Vardhan Naik
      Vardhan Naik

      Thank you so much for the blog. It fixed my issue .

      Additionally run this command as well to reorg all tables

      use NPL

      go

      select "reorg rebuild " + name from sysobjects where type = "U"

      go

      Author's profile photo Baiming Gao
      Baiming Gao

      Thanks for the fix. However, after running the system for a few days, "reorg rebuild" stopped working. Any ideas?

      reorg%20rebuild%20failed

      reorg rebuild failed

      Author's profile photo Martin Kiewitz
      Martin Kiewitz

      Hello Pawet,

       

      I used your guide around a year ago and it all worked fine, I got license updates every few months and that also worked.

      But today trying to login gave me a "rabax during sapgui logon".

      I tried to restart the instance and it says something about no database via r3trans and doesn't start the instance.

      Do you have any idea why that is and how I can fix it? Even if I have to set up another system in another VM, I would like to release my transport otherwise I would lose all my developments.

       

      Kind Regards

       

      Author's profile photo Paweł Wiejkut
      Paweł Wiejkut
      Blog Post Author

      Hello,

      Kindly please check this: https://pawelwiejkut.net/sap-npl-7-52-sp04-dont-start/

      Best regards,

      Pawel.

      Author's profile photo Ene Florin Catalin
      Ene Florin Catalin

      Great post! Thanks a lot for your help!