Initial allocation of tempdb

When you install Adaptive Server, tempdb is 2MB, and is located completely on the master device, as shown in Figure 18-1. This is typically the first database that a System Administrator needs to make larger. The more users on the server, the larger it needs to be. It can be altered onto the master device or other devices. Depending on your needs, you may want to stripe tempdb across several devices.

Figure 18-1: tempdb default allocation

Use sp_helpdb to see the size and status of tempdb. The following example shows tempdb defaults at installation time:

sp_helpdb tempdb
name      db_size  owner  dbid   created     status
--------- -------- ------ ------ ----------- --------------------
tempdb    2.0 MB   sa     2     May 22, 1999 select into/bulkcopy

device_frag  size    usage        free kbytes
------------ -------- ------------ ---------
master       2.0 MB  data and log 1248