Sybase tempdb database


Sybase tempdb database
Prev	About tuning Sybase dataservers	Next

Sybase tempdb is used quite frequently so it should be placed on a separate file system mounting on a dedicated volume. The volume should be striped and its disks should not be shared with other high activity volumes. This database should also bind to its own cache space with the Sybase ASE-named cache feature to reduce paging.

The tempdb database needs to be large enough to contain all the work tables and temporary tables created by the dataserver. When the Adaptive Server is installed, tempdb is created entirely on the master device. The database administrator needs to move tempdb on to larger, dedicated devices. (The default size is 2 MB only.)

To do so, first alter tempdb onto the new device created on the new Quick I/O file. By default, the master device is included in tempdb's logsegment and defaultsegment. To have control on the placement of the log segment and default segment, you need to drop those segments from the master device as shown in the example below.

See the tempdb performance chapter in the Sybase ASE Performance and Tuning Guide.

To change tempdb to a dedicated 200MB device

Create a Quick I/O file:
```
$ qiomkfile -s 200m /new/newtempdb_dev
```

Execute these commands on the Sybase Adaptive Server:

$ isql -Usa -P<sa_password> -S<dataserver_name>
> disk init
> name="newtempdb",
> physname="/newtempdb_dev",
> vnevno=<next_available_number>,
> size=102400
> go
> alter database tempdb on newtempdb=200
> go
> sp_dropsegment "default", tempdb, master
> go
> sp_dropsegment logsegment, tempdb, master
> go

Work tables and other temporary tables in tempdb will now be created on the device newtempdb instead of on the tempdb master device.