To set up different segments in the pubs2 database for automatic expansion, follow this procedure. 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.
Setting up pubs2
Create the database. Enter:
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; you need not be in the specified database. 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
