Dropping Segments

The sp_dropsegment system stored procedure can be used to drop a segment from a database or unmap a segment from a database device.

When used only with a segment name, sp_dropsegment drops the named segment from a database and removes it from all devices. Segments cannot be dropped if they contain database objects. To drop a segment containing database objects, first assign the objects to another segment and then drop the segment.

    To drop a segment from a database

sp_dropsegment segname

where

segname

Identifies the segment to be dropped.

When used with a segment name and a device name, sp_dropsegment removes the segment from that device while leaving it on others, thereby reversing the effects of sp_extendsegment. If you specify a database device, SQL Server does not drop the entire segment; it drops only the portion of the segment that is on the specified database device.

    To remove a segment from a specific database device

sp_dropsegment segname, logical_name

where

segname

Specifies the segment to remove from a particular device.

logical_name

Specifies a database device that you want the segname segment to stop using. This parameter is optional, except when dropping the SYSTEM, DEFAULT, and LOGSEGMENT system segments from a database device.

For example, to remove bigseg from dev2, type:

sp_dropsegment bigseg, dev2

Note Dropping a segment removes it from the list of segments in the database but does not remove the database device from the allocation for that database.

If you drop all segments from a database device, the space is still allocated to the database, but it cannot be used for database objects. DBCC CHECKCATALOG will report "Missing segment in Sysusages segmap". You must use sp_addsegment 'default', logical_name to map the default segment to the database device, or you must create another segment for the database on that device.

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