Dropping the master device from tempdb segments

By default, the system, default, and logsegment segments for tempdb include its 2MB allocation on the master device. When you allocate new devices to tempdb, they automatically become part of all three segments. Once you allocate a second device to tempdb, you can drop the master device from the default and logsegment segments. This way, you can be sure that the worktables and other temporary tables in tempdb do not contend with other uses on the master device.

To drop the master device from the segments:

  1. Alter tempdb onto another device, if you have not already done so. For example:

    alter database tempdb on tune3 = 20
  2. Issue a use tempdb command, and then drop the master device from the segments:

    sp_dropsegment "default", tempdb, master
    sp_dropdegment system, tempdb, master
    sp_dropdegment logsegment, tempdb, master
  3. To verify that the default segment no longer includes the master device, issue this command:

    select dbid, name, segmap
    from sysusages, sysdevices
    where sysdevices.low <= sysusages.size + vstart
      and sysdevices.high >= sysusages.size + vstart -1
      and dbid = 2
      and (status = 2 or status = 3)

    The segmap column should report “1” for any allocations on the master device, indicating that only the system segment still uses the device:

     dbid   name            segmap      
     ------ --------------- ----------- 
          2 master                    1 
          2 tune3                     7