How do I move tempdb off of the Master Device

1.1.3: How do I move tempdb off of the Master Device?
There used to be a section in the FAQ describing how to drop all of tempdb'sdevices physically from the master device. This can make recovery of theserver impossible in case of a serious error and so it strongly recommendedthat you do not do this but simply drop the segments as outlined below.Sybase TS Preferred Method of Moving tempdb off the Master Device.This is the Sybase TS method of removing most activity from the master device: 1. Alter tempdb on another device:
1> alter database tempdb on ...
2> go 2. Use the tempdb:
1> use tempdb
2> go 3. Drop the segments:
1> sp_dropsegment "default", tempdb, master
2> go
1> sp_dropsegment "logsegment", tempdb, master
2> go
1> sp_dropsegment "system", tempdb, master
2> go Note that there is still some activity on the master device. On a three connection test that I ran:
while ( 1 = 1 )
begin
create table #x (col_a int)
drop table #x
end
there was one write per second. Not bad. An Alternative (I recently did some bench marks comparing this method, the previous method and a combination of both. According to sp_sysmon there was no difference
in activity at all. I leave it here just in case it proves useful to
someone.) The idea of this handy script is to simply fill the first 2MB of tempdb thuseffectively blocking anyone else from using it.
The slight gotcha with thisscript, since we're using model, is that all subsequent database creates willalso have tempdb_filler installed.
This is easily remedied by dropping thetable after creating a new database.
This script works because tempdb is rebuilt every time the ASE is rebooted.
Very nice trick!/* this isql script creates a table in the model database.
*//* Since tempdb is created from the model database when the
*//* server is started, this effectively moves the active
*//* portion of tempdb off of the master device.
*/use modelgo/* note: 2k row size */create table tempdb_filler(a char(255) not null,b char(255) not null,c char(255) not null,d char(255) not null,e char(255) not null)go/* insert 1024 rows */declare @i intselect @i = 1while (@i <= 1024)begin insert into tempdb_filler values('a','b','c','d','e')
if (@i % 100 = 0) /* dump the transaction every 100 rows */
dump tran model with truncate_only
select @i=@i+1endgo