sp_dropsegment

Drops a segment from a database or unmaps a segment from a particular database device.

Syntax

sp_dropsegment segname, dbname [, device]

Parameters

Examples

Usage

There are additional considerations when using sp_dropsegment:
  • You can drop a segment if it is not referenced by any table, index, or partition in the specified database.

  • If you:
    • Do not supply device – the segment is dropped from the specified database.

    • Supply device – the segment is no longer mapped to the named database device, but the segment is not dropped.

  • Dropping a segment drops all thresholds associated with that segment.

  • You can only execute sp_dropsegment for the logsegment system segment in single-user mode.

    Note: This command may take a long time to complete in in very large databases.
  • When you unmap a segment from one or more devices, the SAP ASE server drops any thresholds that exceed the total space on the segment. When you unmap the logsegment from one or more devices, the SAP ASE server recalculates the last-chance threshold.

  • sp_placeobject changes future space allocations for a table or index from one segment to another, and removes the references from the original segment. After using sp_placeobject, you can drop the original segment name with sp_dropsegment.

  • For the system segments system, default, and logsegment, you must specify the device name from which you want the segments dropped.

Permissions

The permission checks for sp_dropsegment differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be a user with manage database privilege.

Disabled

With granular permissions disabled, you must be the database owner or a user with sa_role.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

Related reference
sp_addsegment
sp_addthreshold
sp_helpsegment
sp_helpthreshold
sp_placeobject