cancel
Showing results for 
Search instead for 
Did you mean: 

Database Auto Expansion Feature -- Issue when assigning growthby value for segment

Former Member
0 Kudos

Hi All,

We are using the Database Auto Expansion policy for our product, the current architecture is as following : A Device contains multiple segments, with monthly partitions (12 months per segment). We have successfully enabled the Auto Expansion policy for device, and the corresponding segment by issuing following commands

1. For device growthby and max size : sp_dbextend 'set', 'device', my_device, '500m', '50000m'

2. For segment growthby : sp_dbextend 'set', 'database', mydatabase, 'my_segment', '500m'

3. For segment threshold: sp_extend 'set', 'threshold', mydatabase, 'my_segment', '100m'

(Note: Because of the segments is created on same device, so we only need to set the threshold for a device, the others will be applied the same)

Everything works properly when the growthby value of segment is smaller than the current device size. But when I set the growthby value of segment equal or greater than current device size, and the Auto Expansion policy seem not work any more until we modify the device size to the value that is greater than growthby value of segment.

For example: I create a device (my_device) with 500 MB of size, and then I set the growthby value of segment (my_segment  -- created on my_device ) is 512 MB. In theory, the device will grown automatically when beginning to insert the data but it did not. I also added the data to reach the threshold of segment, but again nothing happened. Would you please let me know your idea about this case ?

Thanks

Khoa Tran

Accepted Solutions (0)

Answers (3)

Answers (3)

former_member229302
Participant
0 Kudos

Hi Tran,

The links provided are down at this point.  Try the following link.  This is ASE 16, but all the commands and examples are the same.

Configuring a Database for Automatic Expansion - System Administration Guide: Volume 2 - SAP Library

Former Member
0 Kudos

Hi All,

We currently get another issue with Auto Expansion policy, the sp_dbxt_extend_db procedure seems not work as our expectation. We have designed the database storage with one device and multiple segments on this device (currently we have 5 segment) , each segment contains date range partitions (current we use monthly partitions - there are 12 partitions for each segment)

We have 20 gig of data for multiple partitions of year 2016, so in theory, the segment 2016 should be extended. But we only have systemaudit2015seg was extended  and that make us really confuse. Is there any missing configuration for 2016 ? Why is systemaudit2015seg chosen instead of systemaudir2016seg ?

Please share me your idea about this ?

Thanks

Khoa Tran

former_member229302
Participant
0 Kudos

Hi Tran,

For example: I create a device (my_device) with 500 MB of size, and then I set the growthby value of segment (my_segment  -- created on my_device ) is 512 MB. In theory, the device will grown automatically when beginning to insert the data but it did not. I also added the data to reach the threshold of segment, but again nothing happened. Would you please let me know your idea about this case ?


1. For device growthby and max size : sp_dbextend 'set', 'device', my_device, '500m', '50000m'

2. For segment growthby : sp_dbextend 'set', 'database', mydatabase, 'my_segment', '512m'

3. For segment threshold: sp_extend 'set', 'threshold', mydatabase, 'my_segment', '100m'

The device size must grow before the segment size, therefore you also need a threshold for your device.  No growth will happen without a threshold set.

As Mark Pointed out once you get things setup, you can run a simulation using the following syntax:

sp_dbextend 'simulate', dbname, 'default', '5'

What this does is simulate 5 iterations of extending the database's 'default' segment. The link below will help understand what is needed for configuring auto expansion.

SyBooks Online

Jack

Former Member
0 Kudos

Could someone help to review my question ? I'm struggle with it for a week, and I could not find any solution yet. I have tried to read a logic sp_dbxt_extend_db procedure (under syssystemprocs database), but I cannnot understand the logic . Really appreciate if you can help me on this case.