Getting Information about Devices

sp_helpdevice provides information about the devices in the sysdevices table.

When used without a device name, sp_helpdevice lists all the devices available on SAP ASE. When used with a device name, it lists information about that device. Here, sp_helpdevice is used to report information about the master device:
sp_helpdevice master
device_name  physical_name  description 
-----------  -------------- ------------------------------------------
master        d_master         special, default disk, physical disk, 30 MB
status      cntrltype    vdevno    vpn_low   vpn_high 
------      ----------   -------    -------   --------- 
3           0            0          0         10239
Each row in master..sysdevices describes:
The initial contents of sysdevices are operating-system-dependent. sysdevices entries usually include:

If you installed auditing, there is a separate device for sybsecurity.

The vpn_low and vpn_high columns represent the page numbers that have been assigned to the device. For dump devices, these columns represent the media capacity of the device.

The status field indicates the type of device, whether a disk device is used as a default storage device when users issue a create or alter database command without specifying a database device, disk mirroring information, and dsync settings.

Status bits in sysdevices

Bit

Meaning

1

Default disk (may be used by any create or alter database command that does not specify a location)

2

Physical disk

4

Logical disk (not used)

8

Skip header (used with tape dump devices)

16

Dump device

32

Serial writes

64

Device mirrored

128

Reads mirrored

256

Secondary mirror side only

512

Mirror enabled

2048

Used internally; set after disk unmirror, side = retain

4096

Primary device needs to be unmirrored (used internally)

8192

Secondary device needs to be unmirrored (used internally)

16384

UNIX file device uses dsync setting (writes occur directly to physical media)

For more information about dump devices and sp_addumpdevice, See System Administration Guide: Volume 2 Developing a Backup and Recovery Plan.