The system procedure 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 Adaptive Server. 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, 20 MB
status cntrltype device_number low high ------ ---------- -------------- ------ ------- 3 0 0 0 9999
Each row in master..sysdevices describes:
A dump device (tape, disk, or file) to be used for backing up databases, or
A database device to be used for database storage.
The initial contents of sysdevices are operating-system-dependent. Entries in sysdevices usually include:
One for the master device
One for the sybsystemprocs database, which you can use to store additional databases such as pubs2 and sybsyntax, or for user databases and logs
Two for tape dump devices
If you installed auditing, there will also be a separate device for sybsecurity.
The low and high fields represent the page numbers that have been assigned to the device. For dump devices, they represent the media capacity of the device.
The status field in sysdevices is a bitmap that indicates the type of device, whether a disk device will be 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. The status bits and their meanings are listed in Table 16-2:
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 Chapter 27, “Developing a Backup and Recovery Plan.”
