Configuring a Database for Automatic Expansion

You can set up different segments in a database for automatic expansion.

The example below uses the pubs2 database. Not all these steps are mandatory. For example, you may choose not to set growby or maxsize for individual devices, and to use the system default policies only for the devices.

Create the database:

 create database pubs2 on pubs2_data = "10m" log on pubs2_log = "5m"

Set the growby and maxsize policies for the pubs2_data device at 10MB and 512MB respectively. You can be in any database to set these policies. Enter:
```
 exec sp_dbextend 'set', 'device', pubs2_data, '10m', '512m'
```
The system default growby policy is 10% for devices. Rather than set new policies for the pubs2_log device, you can modify this system default, choosing an appropriate growby value. The pubs2_log then expands at this rate. Enter:
```
exec sp_dbextend 'modify', 'device', 'default', 'growby', '3m'
```
Set the growby rate for the default segment, but do not specify a maximum size. Enter:
```
 exec sp_dbextend 'set', 'database', pubs2, 'default', '5m'
```
The growby rate on the default segment may be different from that on the devices where the segment resides. growby controls the segment’s expansion rate when it is running out of free space, and is used only when you expand the segment.

Set the growby and maxsize variables for the logsegment:

 exec sp_dbextend 'set', 'database', pubs2, 'logsegment', '4m', '100m'

Examine the policies established for various segments in the pubs2 database:
```
exec sp_dbextend 'list', 'database', pubs2
```
Examine the policies in the various devices that pubs2 spans. The pattern specifier for devicename (“%”) picks up all these devices:
```
exec sp_dbextend 'list', 'device', "pubs2%"
```
Install the expansion threshold for the default and logsegments segments in pubs2. This sets up and enables the expansion process, and allows you to choose the free space threshold at which to trigger the expansion process. Enter:
```
 use pubs2
----------------------------------------------------------------    exec sp_dbextend 'set', 'threshold', pubs2, 'default',   '4m'
    exec sp_dbextend   'set', 'threshold',  pubs2,  'logsegment', '3m'
```

Examine the thresholds installed by the commands above.

exec sp_dbextend list, 'threshold'

segment name free pages  free pages (KB) threshold procedure status     
------------ ----------- --------------- ------------------- -----------
default          2048    4096             sp_dbxt_extend_db enabled
logsegment        160  320           sp_thresholdaction lastchance
logsegment       1536  3072          sp_dbxt_extend_db      enabled
Log segment free space currently is 2548 logical pages (5096K).
(1 row affected, return status = 0)

In this output, sp_dbxt_extend_db is the threshold procedure that drives the expansion process at runtime. The expansion thresholds are currently enabled on both the default and logsegment segments.

Use simulate to see the expansion:

exec sp_dbextend 'simulate', pubs2, logsegment
exec sp_dbextend 'simulate', pubs2, 'default', '5'

Use modify to change the policy, if necessary:

exec sp_dbextend 'modify', 'database', pubs2, logsegment, 'growby','10m'

To disable expansion temporarily on a particular segment, use disable:
```
 exec sp_dbextend 'disable', 'database', pubs2, logsegment
```

Examine the state of the expansion policy on databases and devices:

exec sp_dbextend list, 'database'
name        segment    item    value status  
----------- ---------- ------- ----- --------
server-wide (n/a)      (n/a)   (n/a) enabled 
default     (all)      growby  10%   enabled 
pubs2       default    growby  5m    enabled 
pubs2       logsegment growby  10m   disabled
pubs2       logsegment maxsize 100m  disabled
(1 row affected, return status = 0)

The status disabled indicates that the expansion process is currently disabled on the logsegment in pubs2.

exec sp_dbextend list, 'device'
name           segment item   value status
-------------- ------- ------ ----- -------
server-wide    (n/a)   (n/a)  (n/a) enabled
default        (n/a)   growby 3m    enabled
mypubs2_data_0 (n/a)   growby 10m   enabled
mypubs2_data_1 (n/a)   growby 100m  enabled
mypubs2_log_1  (n/a)   growby 20m   enabled
mypubs2_log_2  (n/a)   growby 30m   enabled
(1 row affected, return status = 0)

Use enable to reenable the expansion process:

 exec sp_dbextend 'enable', 'database', pubs2, logsegment