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:
Alter tempdb onto another device, if you have not already done so. For example:
alter database tempdb on tune3 = 20
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
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
