Extending Segments

When all databases and database objects on a SQL Server share a default pool of space (for example, when they share a segment), any table or index can grow to fill the space. To prevent this, use the sp_extendsegment system procedure to increase the size of an existing segment by mapping it to one or more additional database devices.

Before you can extend a segment, you must create the database device you are extending the segment on, and the database you are extending the segment for must have access to that database device.

    To extend a segment

sp_extendsegment segname, logical_name

where

segname

Is the name of the existing segment (previously defined using the sp_segment system stored procedure).

logical_name

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

For example, to add the dev2 database device to an existing segment named bigseg, type:

sp_extendsegment bigseg, dev2

Default segments can also be extended. Since DEFAULT is a Transact-SQL keyword, you must enclose it in quotation marks to extend the default segment for your database. For example:

sp_extendsegment 'default', newdevice

For more information about sp_extendsegment, see the Microsoft SQL Server Transact-SQL Reference.