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.
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:
- 2822555 – Error 1105 on default segment during inserts or create index though space is available – SAP ASE
- 2207667 – Multiple Errors Can’t get a new log page in dbid xx 3475, and 1105 in the tempdb database – SAP ASE
- 2786325 – 1105 due to gap in sysusages
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.
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.
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