SAP AS ABAP 7.52 SP04 and SAP ASE: Setting the Maximum Database Size Manually

We purposely configured the database as small as possible by default, so that it would fit for as many people as possible.

However, this may limit various use cases. This is where the SAP ASE auto expansion feature comes in handy. This allows automatic database free space allocation in predefined increments when a threshold is hit. Further, the maximum database size for automatic expansion can also be set.

To set this up, follow these steps:

1. Log into the operating system as user sybnpl and execute

isql -Usapsa -X -w999 -SNPL

2. Next, switch into the database master by running the command

1> use master

2> go

3. To get an overview about the current setup of automatic database expansion rules, run the command

1>  sp_dbextend listfull

2> go

type     name              segment item    value  status   comment

-------- ----------------- ------- ------- ------ -------- ---------------------------------------------------

feature  server-wide       (n/a)   (n/a)   (n/a)  enabled  set='May 25 2019 12:29PM'

database NPL               system  growby  2000M  enabled  set='May 28 2019  9:54PM'

database NPL               system  maxsize 40000M enabled  set='May 28 2019  9:54PM'

database default           (all)   growby  0      disabled mod='May 25 2019 12:29PM' set='May 25 2019 12:29PM'

device   NPL_data_001      (n/a)   growby  2000M  enabled  set='May 28 2019  9:55PM'

device   NPL_data_001      (n/a)   maxsize 40000M enabled  set='May 28 2019  9:55PM'

device   NPL_data_002      (n/a)   maxsize 0      enabled  set='Jul 11 2018  7:21AM'

device   NPL_data_002      (n/a)   growby  1024M  enabled  set='Jul 11 2018  7:21AM'

device   NPL_data_003      (n/a)   maxsize 0      enabled  set='Jul 11 2018  7:21AM'

device   NPL_data_003      (n/a)   growby  1024M  enabled  set='Jul 11 2018  7:21AM'

device   default           (n/a)   growby  0      enabled  mod='May 25 2019 12:29PM' set='May 25 2019 12:29PM'

device   saptools_data_001 (n/a)   growby  1024M  enabled  set='May 28 2019  9:56PM'

device   saptools_data_001 (n/a)   maxsize 4000M  enabled  set='May 28 2019  9:56PM'

(1 row affected)

(return status = 0)

4.In this example, since you are configuring auto expansion from scratch, you need to clean up the current configuration:

1> sp_dbextend 'clear', 'device', NPL_data_001
2> go
1> sp_dbextend 'clear', 'device' , NPL_data_002
2> go
1> sp_dbextend 'clear', 'device' , NPL_data_003
2> go
1> sp_dbextend 'clear', 'device' , 'default'
2> go
1> sp_dbextend 'clear', 'database' , 'default'
2> go
1> sp_dbextend 'clear', 'device', saptools_data_001
2>	go
1> sp_dbextend 'clear','database' , NPL
2> go

5. Next, we set up rules, such that for database saptools we increment by 1 GB steps as soon as only 200 MB free space is left. Automatic expansion will happen until a maximum size of 5 GB is reached:

1> use saptools
2> go
1> exec sp_dbextend 'set', 'device', saptools_data_001, '1G', '5G'
2> go
1> exec sp_dbextend 'set', 'database', saptools, 'default','1G', '5G'
2> go
1> exec sp_dbextend 'set', 'threshold', saptools, 'default','200M'
2> go
1> exec sp_dbextend 'enable' , 'database', saptools
2> go

6. Similar to the database saptools we are going to setup rules for database NPL. For database NPL the free space threshold will be 1G, the allocation increment will be 5G, the maximum auto expansion database size will be 40G:

1> use NPL
2> go
1> exec sp_dbextend 'set', 'device', NPL_data_001, '5G', '40G'
2> go
1> exec sp_dbextend 'set', 'database', NPL, 'default','5G', '40G'
2> go
1> exec sp_dbextend 'set', 'threshold', NPL, 'default','1G'
2> go
1> exec sp_dbextend 'enable' , 'database', NPL
2> go
1> sp_dbextend 'simulate', NPL, 'default', '2'
2> go