sp_extendsegment System Stored Procedure

Extends the range of a segment to another database device.

Syntax

sp_extendsegment segname, logical_name

where

segname

Is the name of the existing segment (previously defined by the sp_addsegment system stored procedure).

logical_name

Specifies the database device to be added to the current database device range for segname.

Remarks

Once a segment has been defined, you can put a table or index on it by using the CREATE TABLE and CREATE INDEX statements. If a table or index is created on a particular segment, all subsequent data for that table or index will be located on that segment.

To associate a segment with a database device, the database must have been created or altered with a reference to that device. A database device can have more than one segment associated with it, and a segment can be extended over several database devices.

Extending the log segment to a new device unmaps any existing segments from that device.

Because sp_addsegment and sp_extendsegment do not accept a size parameter, when a segment is created it is mapped to the entire amount of space allocated for the specified database on the specified device. When the database is expanded with ALTER DATABASE, any segments mapped to that device for the database are expanded to all allocated space.

Example

This example extends the range of the segment INDEXES to the database device DEV2.

sp_extendsegment INDEXES, DEV2

Permission

Execute permission defaults to the database owner.

Tables Used

syssegments, master.dbo.sysdevices, master.dbo.sysusages

See Also