sp_dbextend

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

set – sets the threshold at which a database, segment, or device should fire. The arguments are:
- 'threshold', dbname, segmentname, freespace – 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', dbname, segmentname {[ [, growby ] [, maxsize ] ]} – 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', devicename { [ [, growby ] [, maxsize ] ] }] – 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.
reload does not delete user-specified policies.
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 – shows tasks that currently cause blocking of the expansion process.
- all – shows 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.
maxsize is the maximum size of the segment at which the automatic expansion process stops, not the maximum size of the database.

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.

Examples

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

Usage

There are additional considerations when using sp_dbextend:

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
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.
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 with patterns that 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.
Provide dbname and segmentname to obtain policy rules for individual databases and for the segments inside them.
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.
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.
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.

See also alter database, create database, disk init, disk resize in Reference Manual: Commands.

Permissions

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. A user with sa_role privilege should run the set threshold command when installing the threshold procedure.

The following permission checks for sp_dbextend differ based on your granular permissions settings

Setting	Description
Enabled	With granular permissions enabled: sp_dbextend clear database – You must be a user with `own any database` privilege, or for the specified database, you must be the database owner or a user with `own database` privilege on the database. sp_dbextend clear device – You must be a user with `manage disk` privilege . sp_dbextend clear threshold – You must be the database owner or a user with `own` `database` privilege on the database. sp_dbextend execute – You must be the database owner or be a user with `own database` privilege on the specified database, and you must have `manage disk` privilege. sp_dbextend simulate – You must be the database owner or a user with `own database` privilege. sp_dbextend enable/disable – You must be a user with `own any database` privilege or the database owner or have the `own database` privilege on the specified database. sp_dbextend list database – You must be a user with `own any database` privilege when % pattern is specified. sp_dbextend list @ verbose=2 – You must be a user with `own any database` privilege. sp_dbextend modify database – You must be the database owner or a user with `own database` privilege on the specified database or a user with `own any database` privilege for sp_dbextend 'modify', 'database', 'default'. sp_dbextend modify device – You must be the database owner or a user with `manage disk` privilege. sp_dbextend reload defaults – You must be a user with `own any database` privilege. sp_dbextend set database – You must be the database owner or a user with `own database` privilege on the specified database. sp_dbextend set device – You must be a user with `manage disk` privilege. sp_dbextend set threshold – You must be the database owner or a user with `own database` on the specified database and you must have the `manage disk` privilege. sp_dbextend trace – You must be a user with `set switch` privilege.
Disabled	With granular permissions disabled: sp_dbextend clear database – You must be the database owner or a user with sa_role. sp_dbextend clear device – You must be a user with sa_role. sp_dbextend clear threshold – You must be the database owner or a user with sa_role. sp_dbextend execute – You must be a user with sa_role. sp_dbextend simulate – You must be the database owner or a user with sa_role. sp_dbextend enable/disable – You must be the database owner or a user with sa_role. sp_dbextend list database – You must be a user with sa_role permission when % pattern is specified. sp_dbextend list @ verbose=2 – You must be a user with sa_role. sp_dbextend modify database – You must be the database owner or a user with sa_role if `dbname` equals default. sp_dbextend modify device – You must be the database owner or a user with sa_role. sp_dbextend reload defaults – You must be the database owner or a user with sa_role. sp_dbextend set database – You must be the database owner or a user with sa_role. sp_dbextend set device – You must be the database owner or a user with sa_role. sp_dbextend set threshold – You must be the database owner or a user with sa_role. sp_dbextend trace – You must be a user with sa_role.

Setting

Description

Enabled

With granular permissions enabled:

sp_dbextend clear database – You must be a user with own any database privilege, or for the specified database, you must be the database owner or a user with own database privilege on the database.
sp_dbextend clear device – You must be a user with manage disk privilege .
sp_dbextend clear threshold – You must be the database owner or a user with own database privilege on the database.
sp_dbextend execute – You must be the database owner or be a user with own database privilege on the specified database, and you must have manage disk privilege.
sp_dbextend simulate – You must be the database owner or a user with own database privilege.
sp_dbextend enable/disable – You must be a user with own any database privilege or the database owner or have the own database privilege on the specified database.
sp_dbextend list database – You must be a user with own any database privilege when % pattern is specified.
sp_dbextend list @ verbose=2 – You must be a user with own any database privilege.
sp_dbextend modify database – You must be the database owner or a user with own database privilege on the specified database or a user with own any database privilege for sp_dbextend 'modify', 'database', 'default'.
sp_dbextend modify device – You must be the database owner or a user with manage disk privilege.
sp_dbextend reload defaults – You must be a user with own any database privilege.
sp_dbextend set database – You must be the database owner or a user with own database privilege on the specified database.
sp_dbextend set device – You must be a user with manage disk privilege.
sp_dbextend set threshold – You must be the database owner or a user with own database on the specified database and you must have the manage disk privilege.
sp_dbextend trace – You must be a user with set switch privilege.

Disabled

With granular permissions disabled:

sp_dbextend clear database – You must be the database owner or a user with sa_role.
sp_dbextend clear device – You must be a user with sa_role.
sp_dbextend clear threshold – You must be the database owner or a user with sa_role.
sp_dbextend execute – You must be a user with sa_role.
sp_dbextend simulate – You must be the database owner or a user with sa_role.
sp_dbextend enable/disable – You must be the database owner or a user with sa_role.
sp_dbextend list database – You must be a user with sa_role permission when % pattern is specified.
sp_dbextend list @ verbose=2 – You must be a user with sa_role.
sp_dbextend modify database – You must be the database owner or a user with sa_role if dbname equals default.
sp_dbextend modify device – You must be the database owner or a user with sa_role.
sp_dbextend reload defaults – You must be the database owner or a user with sa_role.
sp_dbextend set database – You must be the database owner or a user with sa_role.
sp_dbextend set device – You must be the database owner or a user with sa_role.
sp_dbextend set threshold – You must be the database owner or a user with sa_role.
sp_dbextend trace – You must be a user with sa_role.

Auditing

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

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	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