By default, the system, default, and logsegment segments for tempdb include its 4MB allocation on the master device. When you allocate new devices to tempdb, they automatically become part of all three segments unless you add them as dedicated data or log. Once you allocate a second device to tempdb, you can drop the master device from the default, system, 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_dropsegment "system", tempdb, master
sp_dropsegment "logsegment", tempdb, master
To verify the segments no longer include the master device, issue this command against the master database:
select dbid, name, segmap from sysusages, sysdevices where sysdevices.vdevno= sysusages.vdevno and dbid = 2 and (status&2=2 or status&3=3))
The segmap column should report “0” for any allocations on the master device, indicating that no segment allocations exist:
dbid name segmap ------ --------------- ----------- 2 master 0 2 tune3 7
Alternatively, issue:
use tempdb sp_helpdb 'tempdb'
device_fragments size usage created free kbytes ----------------- ------ ---------- ----------------- ---------- master 4.0 MB data only Feb 7 2008 2:18AM 2376 tune3 20.0 MB data and log May 16 2008 1:55PM 16212 device segment --------- ----------------------------- master -- unused by any segments -- tune3 default tune3 logsegment tune3 system
