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

Examples

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

SettingDescription
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:

InformationValues
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

Related reference
sp_dropthreshold
sp_modifythreshold