sp_deviceattr

(UNIX platforms only) Changes the device parameter settings of an existing database device file.

Syntax

sp_deviceattr  logicalname, optname, optvalue

Parameters

Examples

Usage

There are additional considerations when using sp_deviceattr:
  • For database devices stored on UNIX files, dsync determines whether updates to the device take place directly on the storage media, or are buffered by the UNIX file system.

    When dsync is on, writes to the database device occur directly to the physical storage media, and the SAP ASE server can recover data on the device in the event of a system failure.

    When dsync is off, writes to the database device may be buffered by the UNIX file system. The UNIX file system may mark an update as being completed, even though the physical media has not yet been modified. In the event of a system failure, there is no guarantee that requests to update data have ever taken place on the physical media, and the SAP ASE server may be unable to recover the database.

  • (UNIX only) On raw devices, you cannot set directio or dsync via the sp_deviceattr stored procedure to true.

    Note: For HPUX, only the dsync option applies.
    Doing so returns a message such as:
    You cannot set option dsync for raw device 'dev/raw/raw235'
    or
    You cannot set attribute dsync for raw device 'myrawdisk1'
  • After using sp_deviceattr to change the dsync or directio setting, you must restart the SAP ASE server before the change takes affect.

  • sp_deviceattr displays a warning message if the dsync option is disabled for a database device file.

    directio setting, you must restart the SAP ASE server before the change takes affect.

  • dsync is always on for the master device file. You cannot change the dsync setting for a master device file with sp_deviceattr.Therefore, you cannot set the directio option for the master device.

  • Turn off the dsync value only when the databases on the device does not need to be recovered after a system failure. For example, you may consider turning dsync off for a device that stores only the tempdb database.

  • The SAP ASE server ignores the dsync setting for devices stored on raw partitions; updates to those devices are never buffered, regardless of the dsync setting.

  • dsync is not used on the Windows platform.

Permissions

The permission checks for sp_deviceattr differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must be a user with manage disk privilege.

Disabled

With granular permissions disabled, you must be a user with sa_role.

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_helpdevice