Allows you to:
Install automatic database expansion procedures on database/segment pairs and devices.
Define site-specific policies for individual segments and devices.
Simulate execution of the database expansion machinery, to study the operation before engaging large volume loads.
These policies are stored in the sysattributes table in master database.
All arguments are string arguments:
sp_dbextend ’help’[, <command > ]
sp_dbextend [ ['set', ['threshold', dbname, segmentname, freespace |
'database', dbname, segmentname {[ [, growby ] [, maxsize ] ]} |
'device', devicename { [ [, growby ] [, maxsize ] ] }] |
'clear', 'threshold', dbname, segmentname
sp_dbextend 'clear', 'database' [, dbname [, segmentname ] ]
sp_dbextend 'clear', 'device' [, devicename ]
sp_dbextend 'modify', 'database', dbname, segmentname,
{ 'growby' | 'maxsize' }, newvalue
sp_dbextend 'modify', 'device', devicename, { ’growby’ | ' maxsize ' },
newvalue
sp_dbextend { 'list' | 'listfull' } [, 'database' [, dbname [, segmentname
[, order_by_clause ] ] ] ]
sp_dbextend { 'list' | 'listfull' } [, 'device' [, devicename [, order_by_clause ] ] ]
sp_dbextend 'check', 'database' [, dbname [, segmentname ] ]
sp_dbextend { 'simulate' | 'execute' }, dbname, segmentname [, iterations ]
sp_dbextend 'trace', {'on' | 'off' }
sp_dbextend 'reload [defaults]'
sp_dbextend { 'enable' | 'disable' }, 'database' [, dbname [, segmentname ] ]
sp_dbextend 'who' [, '<spid>' | 'block' | 'all' ]
sets the threshold at which a database, segment, or device should fire. The arguments are:
threshold – specifies the free space level at which to install the threshold on a specified database and segment.
You should always specify freespace in size unit specifiers, such as megabytes. If you specify no size units, the value of freespace is treated as the number of kilobytes in the segment.
database – specifies the name of the database/segment pair, the size by which to alter the database, and the maximum size of the database, at which the expansion process stops.
growby – is the rate, in unit specifiers or percentage values, at which the database grows at each expansion attempt. maxsize is the maximum size of the segment, after which no further expansion occurs. Both are optional parameters.
device – defines the growth rate and maximum size of a device, in unit specifiers or percentage values, at which the device can grow. maxsize in devices is subject to OS disk limitations.
clears any previously set rules of expansion for a specified database and segment or for a specified device.
modifies previously set site-specific policies, such as growby and maxsize, for a database and segment.
Use newvalue to specify the new value you set for automatic expansion.
lists briefly existing rules for a specified database, segment, or device, and presents the data from master.db.sysattributes in a readable format. Allows you to view rules on a per-database or per-device basis.
Presents the current rules in effect.
Use order_by_clause to generate listings in a different order from the default ordering of name, type.
lists fully the site-specific policy rules, and includes a comment column in the sysattributes table that displays a datetime stamp for when the rule was set, and when it was last modified.
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.
simulates executing the database or device expansion schemes executed at runtime, according to the set of current policies implemented by the set command.
iterations specifies the number of times you simulate the expansion.
performs the actual database/segment, or device, expansion, using the current set of policies.
reinitializes sysattributes with the system-supplied defaults for growby and maxsize in all databases, segments, and devices, and reverts the databases or devices to the original default behavior.
provides help information for all command parameters, such as set or list, or help information for any single command.
traces the threshold procedure execution logic in all expansion processes.
enables or disables the automatic expansion procedures on a specified database segment or device.
shows any active expansion processes running currently. ‘<spid>’ restricts the output for a particular spid. Use:
block to show tasks that currently cause blocking of the expansion process.
all to show all currently active tasks.
specifies the free space value at which the threshold procedure is installed on the specified segment. Always use size unit specifiers, such as megabytes, to specify freespace.
is the name of the database in which the threshold is being installed.
is the segment contained in database dbname.
is the logical name of the affected device.
specifies the new value you set for automatic expansion when you modify a policy for a database/segment pair or device.
generates listings in a different order from the default ordering in the list command. The default order is name, type.
specifies the number of times an expansion is simulated or executed.
specifies the rate, in unit specifiers or percentage values, at which a specified database segment or device grows each time the threshold procedures are attempted.
is the maximum size of a segment/database pair or device, the size at which automatic expansion must stop.
set thresholds – installs the space expansion threshold on a log segment in the database pubs2 at 100MB:
sp_dbextend 'set', 'thresh’, pubs2, logsegment, '100m'
set database – installs a policy for the logsegment segment, at a growth rate of 100MB per expansion attempt:
sp_dbextend 'set', 'database', pubs2, logsegment, '100m'
set device – expands this device until either the OS disk space limitation or the device size of 32GB is reached:
sp_dbextend 'set', 'device', pubs2-datadev1, '100m'
clear – shows how to clear all space-expansion thresholds previously installed in pubs2, logsegment:
sp_dbextend 'clear', 'thresh', pubs2, logsegment
You can also the space-expansion threshold for segment dataseg1 in pubs2, installed at a free space of 200MB:
sp_dbextend 'clear', 'thresh', pubs2, dataseg1, '200m'
modify – defines the rate of growth as 5% of current value, in each expansion attempt:
sp_dbextend 'modify', 'da', pubs2, logsegment, 'growby', '5%'
A command can fail when maxsize is not previously defined:
sp_dbextend 'modify', 'device', pubs2_log_dev, 'maxsize', '2.3g'
list – lists briefly the rules for all databases and devices:
sp_dbextend 'list'
This lists rules for all databases with names similar to ‘pubs%’:
sp_dbextend 'list', 'database', 'pubs%'
listfull – lists the rules for all databases and devices, including a comment column showing a datetime stamp:
sp_dbextend 'listfull'
simulate – simulates an expansion twice, without tripping the thresholds:
sp_dbextend 'simulate', pubs2, logsegment, '2'
execute – executes an automatic expansion procedure:
sp_dbextend 'execute', pubs2, logsegment
help – obtains help for a specific command:
sp_dbextend help, 'set'
You can only set one automatic expansion threshold on any given database/segment pair. If you try to install another instance of the threshold procedure, even at a different free space value, an error is raised.
You cannot set system-supplied defaults, only modify them. After you modify system defaults you can reset them by re-running the installdbextend script, or by using the reload defaults command.
To disallow any automatic growth in a particular segment, either specify 0 for growby or maxsize, or do not install the threshold procedure at all. If you specify NULL for this parameter, defaults to the system-specified default growby rate is used.
maxsize is the maximum size of the segment at which the automatic expansion process stops, not the maximum size of the database.
There is no system-specified maximum size for the default database. If no maxsize value is specified, the size of the database is limited only by the physical limitations of the database device.
To turn off the automatic growth feature on a particular device, specify 0 for growby or maxsize. If you do not specify a value for growby, the default expansion rate is used.
You can set maxsize to a value larger than the total amount of disk space available on the device, but actual expansion is limited to the available disk space at the time expansion is attempted.
When you use this stored procedure to clear a threshold, dbname and segmentname are required arguments.
When you use this stored procedure to clear a database, and provide no dbname and segmentname, all policy rules—that is, all the relevant rows in master.dbo.sysattributes—for the current database and all segments in it are deleted. This is a good way to reverse all settings to default and restart.
When you use this stored procedure to clear a device, if you do not provide a value for devicename, no policy rules are cleared. You can clear out the policy rules for a single device by providing devicename or using “%” to clear policies for all devices.
You can specify dbname, devicename, and segmentname using patterns, so that names whose patterns match the specified pattern are considered for the clear, enable, disable, and list operations.
You must have set a value or property before you can modify it. modify fails if no value was previously set. growby and maxsize are modified to the new value specified by newvalue
The new value specified in newvalue remains in effect throughout subsequent attempts to expand either the database or device. Even if newvalue is less than the current size of the database, segment, or device, the object does not shrink. newvalue specifies only future expansion, and does not affect current sizes.
When you use list for a database and provide no dbname or segmentname, all the policy rules (that is, rows in master.dbo.sysattributes) for all segments in the current database are listed.
Provide dbname and segmentname to obtain policy rules for individual databases and for the segments inside them.
When you use list for a device name and provide no devicename, default policy rules for all devices are listed. You can filter this to list the policy rules for a single device by providing devicename or use pattern specifiers for the devicename.
You can simulate the expansion of only one database/segment pair at a time. Both dbname and segmentname are required arguments. You cannot use wildcard patterns in dbname or segmentname for execute or simulate commands.
The maximum size of a device is 32Gb.
Use reload to re-initialize your databases and devices after using modify and simulate. reload deletes any existing rows in master.dbo.sysattributes that describe system default behavior, and loads new rows.
reload does not delete user-specified policies.
trace turns the trace facility on or off throughout the server. If trace is on, messages appear in the server error log when a threshold fires. Use trace only for troubleshooting.
sa_role permission is needed to run the installdbextend script, and execute permission is granted to public only on sp_dbextend.
Any user can execute the list parameter. All other commands must be granted Database Owner or sa_role permissions on the specified database.
Commands such as clear, that allow pattern specifiers for the dbname argument, require sa_role privilege.
The following command parameters require sa_role privilege: simulate, execute, check, reload defaults, trace.
If the automatic expansion procedures are installed on a segment by a Database Owner without sa_role privilege, the devices do not expand, because the user cannot run the disk resize command. Sybase recommends that a user with sa_role privilege run the set threshold command when installing the threshold procedure.
Stored procedures sp_dropthreshold, sp_modifythreshold
Commands alter database, create database, disk init, disk resize
