1 Version 4.0 -- 1/15/89 sp_helpdevice
______________________________________________________________________
NAME: sp_helpdevice
FUNCTION:
Reports information about SQL Server's database devices and dump
devices.
SYNTAX:
sp_helpdevice [devname]
EXAMPLES:
1) sp_helpdevice
sp_helpdevice Version 4.0 -- 1/15/89 2
______________________________________________________________________
Reports information about all the devices on SQL Server.
For example:
device_name physical_name description
status cntrltype device_number low high
------------ ------------------- -----------------------------------------
diskdump null disk, dump device
16 2 0 0 20000
master the_master_device special, default disk, physical disk, 10 MB
3 0 0 0 5120
(3 rows affected)
2) sp_helpdevice diskdump
Reports information about the dump device named diskdump.
3 Version 4.0 -- 1/15/89 sp_helpdevice
______________________________________________________________________
PARAMETERS:
devname - is the logical name of the device about which you want
information. If you omit this parameter, information on all
the devices is displayed.
COMMENTS:
o Executing 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.
o The sysdevices table contains dump devices (tapes or disks to
which you can dump databases with DUMP DATABASE and transaction
logs with DUMP TRANsaction) and database devices (devices on
which databases are stored).
Database devices can be designated default devices, which means
sp_helpdevice Version 4.0 -- 1/15/89 4
______________________________________________________________________
that they can be used for database storage when a user issues
the CREATE DATABASE or ALTER DATABASE command 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.
o Database devices are added to the system with the DISK INIT
command. Dump devices are added with the system procedure
sp_addumpdevice.
o The status column in the report from sp_helpdevice contains a
number that corresponds to the status description in the
description column.
The cntrltype column specifies the controller number of the
device. For disk dump devices, the controller number is 2; for
disk dump devices it can be 3 through 8. For database devices,
it is almost always 0 (unless your installation has a special
5 Version 4.0 -- 1/15/89 sp_helpdevice
______________________________________________________________________
type of disk controller.)
The device_number column is 0 for dump devices, 0 for the mas-
ter database device, and between 1 and 255 for other database
devices.
The low and high columns represent virtual page numbers, each
of which is unique among all the devices on SQL Server.
MESSAGES:
The device name supplied for the devname parameter doesn't exist
on SQL Server. Run the procedure without the devname parame-
ter to see a list of all devices.
PERMISSIONS:
Execute permission to public.
sp_helpdevice Version 4.0 -- 1/15/89 6
______________________________________________________________________
TABLES USED:
#spdevtab, master.dbo.spt_values, master.dbo.sysdevices
SEE ALSO:
sp_addumpdevice, sp_configure, sp_diskdefault, sp_dropdevice,
sp_helpdb, sp_logdevice, sp_who, DISK INIT, DUMP DATABASE, DUMP
TRANsaction, LOAD DATABASE, LOAD TRANsaction