sp_helpdevice

Reports information about a particular device or about all SAP ASE database devices and dump devices.

Syntax

sp_helpdevice [devname]

Parameters

Examples

Usage

There are additional considerations when using sp_helpdevice.
  • sp_helpdevice displays the amount of unallocated space per device, indicated by the placeholder Free in the description column in the ouput

    Note: A small amount of space can remain unused on a device, especially for servers with larger page sizes. For example, the last 2MB of a 250MB device in a 16K server cannot be allocated, and sp_helpdevice reports this as free. This is because the size of an allocation unit in a 16K server is 4Mb, so only multiples of allocation units can be allocated.
  • sp_helpdevice displays information on the specified device, when devname is given, or on all devices in master.dbo.sysdevices, when no argument is given.

  • The sysdevices table contains dump devices and database devices.

    Database devices can be designated as default devices, which means that they can be used for database storage. This can occur when a user issues create database or alter database and does not specify a database device name or gives the keyword default. To make a database device a default database device, execute the system procedure sp_diskdefault.

  • Add database devices to the system with disk init. Add dump devices with sp_addumpdevice.

  • If you issue sp_helpdevice against a single device, it displays a list of allocated fragments on that device.

  • The description column displays information about device types:
    • block device

    • file system device

    • raw device

    The number in the status column corresponds to the status description in the “description” column.

    The cntrltype column specifies the controller number of the device. The cntrltype is 2 for disk or file dump devices and 3–8 for tape dump devices. For database devices, the cntrltype is usually 0 (unless your installation has a special type of disk controller).

    The vdevno column is 0 for dump devices, 0 for the master database device, and 1 or higher for other database devices.

    The vpn_low and vpn_high columns represent virtual page numbers, each of which is unique among all the devices in SAP ASE.

See also disk init, dump database, dump transaction, load database, load transaction in Reference Manual: Commands.

Permissions

Any user can execute sp_helpdevice. Permission checks do not differ based on the granular permissions settings.

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_addumpdevice
sp_deviceattr
sp_diskdefault
sp_dropdevice
sp_logdevice