Reports information about a particular device or about all Adaptive Server database devices and dump devices.
sp_helpdevice [devname]
is the name of the device about which to report information. If you omit this parameter, sp_helpdevice reports on all devices.
Displays information about all the devices on Adaptive Server:
1> sp_helpdevice 2> go
device_name physical_name description status cntrltype vdevno vpn_low vpn_high ----------- --------------------- ---------------------------------------- ------ --------- ----------- ----------- ----------- dev1 d:\sybdata\RV150.dev1 special, dsync off, directio on, physical disk, 150.00 MB, Free: 0.00 MB 2 0 2 0 76799 dev2 d:\sybdata\RV150.dev2 special, dsync on, directio off, physical disk, 150.00 MB, Free: 130.00 MB 16386 0 3 0 76799 master d:\sybdata\RV150.mas special, dsync on, directio off, default disk, physical disk, 30.00 MB, Free: 0.50 MB 3 0 0 0 15359 sysprocsdev d:\sybdata\RV150.ssp special, dsync on, directio off, physical disk, 120.00 MB, Free: 0.00 MB 16386 0 1 0 61439 tapedump1 \\.\TAPE0 disk, dump device 16 2 0 0 20000 tapedump2 \\.\TAPE1 tape, 625 MB, dump device 16 3 0 0 20000 (6 rows affected, return status = 0)
Reports information about the dump device named diskdump:
sp_helpdevice diskdump
sp_helpdevice displays
the amount of unallocated space per device, indicated by the placeholder Free in
the description column in the ouput
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 the device type. The device types are:
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 Adaptive Server.
Any user can execute sp_helpdevice.
Values in event and extrainfo columns from the sysaudits table are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
38 |
exec_procedure |
Execution of a procedure |
|
Commands disk init, dump database, dump transaction, load database, load transaction
System procedures sp_addumpdevice, sp_deviceattr, sp_diskdefault, sp_dropdevice, sp_logdevice
