sp_dbextend

Description

Allows you to:

These policies are stored in the sysattributes table in master database.

All arguments are string arguments:

Syntax

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 { 'list' | 'listfull' }, [ 'threshold' [ , @dbname 
	[ , @segmentname ] ] ]
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' ]

Parameters

set

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.

clear

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

modify

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.

list

lists briefly existing rules for a specified database, segment, device, or thresholds on specified segments, and presents the data from master.dbo.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.

Use threshold to display all the thresholds that are currently installed on the specified database (using the @dbname) and segment (using @segment name).

listfull

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.

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 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.

execute

performs the actual database/segment, or device, expansion, using the current set of policies.

reload defaults

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.

help

provides help information for all command parameters, such as set or list, or help information for any single command.

trace

traces the threshold procedure execution logic in all expansion processes.

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. Use:

  • block to show tasks that currently cause blocking of the expansion process.

  • all to show all currently active tasks.

freespace

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.

dbname

is the name of the database in which the threshold is being installed.

segmentname

is the segment contained in database dbname.

devicename

is the logical name of the affected device.

newvalue

specifies the new value you set for automatic expansion when you modify a policy for a database/segment pair or device.

order_by_clause

generates listings in a different order from the default ordering in the list command. The default order is name, type.

iterations

specifies the number of times an expansion is simulated or executed.

growby

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.

maxsize

is the maximum size of a segment/database pair or device, the size at which automatic expansion must stop.

Examples

Example 1

set thresholds – installs the space expansion threshold on a log segment in the database pubs2 at 100MB:

sp_dbextend 'set', 'thresh', pubs2, logsegment, '100m'

Example 2

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'

Example 3

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'

Example 4

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'

Example 5

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'

Example 6

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%'

Example 7

listfull – lists the rules for all databases and devices, including a comment column showing a datetime stamp:

sp_dbextend 'listfull'

Example 8

list threshold – when issued from the pubs2 database, this lists the thresholds setup on various segments in the pubs2 database:

sp_dbextend 'list', 'threshold'

To examine the thresholds on a particular segment, use as:

sp_dbextend 'list', 'threshold', pubs2, 'logsegment'

Example 9

simulate – simulates an expansion twice, without tripping the thresholds:

sp_dbextend 'simulate', pubs2, logsegment, '2'

Example 10

execute – executes an automatic expansion procedure:

sp_dbextend 'execute', pubs2, logsegment

Example 11

help – obtains help for a specific command:

sp_dbextend help, 'set'

Usage

Permissions

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.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

Stored procedures sp_dropthreshold, sp_modifythreshold

Commands alter database, create database, disk init, disk resize