sp_dbextend

Description

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:

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

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

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

• By default, if the size of the device is greater than 40MB, the size of the database is increased by 10 percent. If your datebase is smaller than 40MB, the size of the database is increased by 4MB. However, you can specify database resizing limits that match the needs of your site

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

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:

See also