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
