Monday 11 April 2011

ASE : How do I move tempdb off of 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

No comments:

Post a Comment