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 assign the objects to another segment or drop the objects first and then drop the segment.
The syntax for dropping a segment is:
sp_dropsegment segname, dbname
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–see “Reducing the scope of a segment”.
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
