Command options in the sp_dbextend interface

sp_dbextend has the following syntax:

sp_dbextend [ command [, arguments...] ]

where command is one of the options discussed below, and arguments specifies the database name, segment name, and amount of free space, among other things. For more information about sp_dbextend, see the Reference Manual.

The following command parameters define and specify user choices in sp_dbextend:

set – sets the threshold at which a database and segment pair should fire. This command also allows you to specify the size and define the growth rate by which to expand the database and segment or device at each attempt, either in size values or as a percentage of the size of the device when the expansion is attempted.

You can also use set to specify the maximum allowable size for the database, set the site-specific policy rules for expanding a device, and set the maximum size for the device.

clear – clears any previously set rules of expansion for a specified database and segment, or for a specified device.

Using clear deletes the affected rows from master.db.systattributes, but automatic expansion is still in effect. Default rules for searching the devices a segment maps onto still apply when you consider where to expand the database, which device to expand, and in what order.

For instance, if you issue clear for one device, only that device continues as a candidate for expansion under the default expansion rules. Other devices expand according to their individual device characteristics.

To turn the automatic expansion feature completely off in a given database and the devices that database resides on, use the command ’clear’, ’threshold’. This command executes sp_dropthreshold.

modify – modifies site-specific policies, such as growby and maxsize in an existing entry for a database and segment. You can also use modify to modify system-supplied defaults.

There is no support for modifying existing thresholds at which the database expansion threshold procedure fires. Use the existing interface sp_modifythreshold to make modifications.

list – lists any existing rules for a specified database, segment, or device, and presents the data from master.db.sysattributes in a readable format.

list allows you to view site-specific current policy rules in effect for each database, segment, or device previously configured by a set command. Databases, segments, and devices that do not appear in the output are subject to the default rules of expansion, which list also displays.

listfull – lists fully the site-specific rules, including a comment column in the sysattributes table that displays a datetime stamp for when the rule was set and when it was last modified.

check – examines current policies and verifies that they are consistent with the current space layout in each segment. If any policy settings appear redundant, ineffective, or incorrect, a warning message appears.

simulate – simulates the database or device expansion schemes executed at runtime, according to the set of current policies implemented by the set command.

You can perform these cycles of execution as many times as you like, to study the way database and disk expansion operate and to determine what pieces expand, and by how much space. To perform the expansion, use the execute parameter.

execute – performs the database and segment, or device, expansion using the current set of policies.This option performs the expansion process immediately, irrespective of the current free space in the specified segment.

reload [defaults] – reinitializes sysattributes with the system-supplied defaults for the growby and maxsize parameters, in all databases, segments, and devices, to revert to the original default behavior.

For instance, you might make changes to system default rules using modify, and then run a simulation with simulate. Running reload [defaults] deletes any existing rows describing system default behavior and loads new rows. It does not change existing rows defining user-specified policies.

help – provides help in using the procedure, or specific help information for each command.

trace – turns tracing facility on or off, in order to trace through the procedure execution.

enable / disable – enables or disables the automatic expansion procedures on a specified database segment or device.

who – shows any active expansion processes running currently. ‘<spid>’ restricts the output for a particular spid.

If you provide no argument, sp_dbextend defaults to help. For more information on sp_dbextend, see the Reference Manual.

The automatic expansion procedure does not create new devices; it only alters the size of the database and segment on existing devices to which the segment currently maps.