Dropping Segments

When you use sp_dropsegment with only a segment name and the database name, the named segment is dropped from the database.

However, you cannot drop a segment as long as database objects are still assigned to it. You must first assign the objects to another segments or drop the objects, then drop the segment.

You cannot completely drop the default, system, or log segment from a database. A database must have at least one default, system, and log segment. You can, however, reduce the scope of these segments.

Note: Dropping a segment removes its name from the list of segments in the database, but it does not remove database devices from the allocation for that database, nor does it remove objects from devices. If you drop all segments from a database device, the space is still allocated to the database but cannot be used for database objects. dbcc checkcatalog reports “Missing segment in Sysusages segmap.” To make a device available to a database, use sp_extendsegment to map the device to the database’s default segment:

sp_extendsegment "default", dbname, devname

See the Reference Manual: Procedures.