UNIX platforms only Changes the dsync setting of an existing database device file.
sp_deviceattr logicalname, optname, optvalue
is the name of an existing database device. The device can be stored on either an operating system file or a raw partition, but the dsync setting is ignored for raw partitions.
is the name of the setting to change. Currently, the only acceptable value for optname is dsync.
can be either “true” or “false.”
Sets dsync on for the device named “file_device1”:
sp_deviceattr file_device1, dsync, true
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 Adaptive 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 Adaptive Server may be unable to recover the database.
After using sp_deviceattr to change the dsync setting, you must reboot Adaptive 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.
The dsync value should be turned off only when the databases on the device need not be recovered after a system failure. For example, you may consider turning dsync off for a device that stores only the tempdb database.
Adaptive 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 NT platform.
The user executing sp_deviceattr must have permission to update the sysdevices table.
System procedures sp_helpdevice