Q1.7: How do I move tempdb off of the Master Device?

Note: I received a message from Sybase TS recommending that the FAQ no longer advocate the physical removal of entries from the sysusages/sysdatabases tables. It makes recovery extremely painful.
After reviewing their write-up I agree.

A quick alternative - Sybase TS Preferred Method

This is the Sybase TS method of removing most activity off of the master device:

Alter tempdb on another device:

 1> alter database tempdb on ...
 2> go

Use the tempdb:
```
 1> use tempdb
 2> go
```

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.

Yet another alternative

The idea of this handy script is to simply fill the first 2MB of tempdb thus effectively blocking anyone else from using it. The slight gotcha with this script, since we're using model, is that all subsequent database creates will also have tempdb_filler installed. This is easily remedied by dropping the table after creating a new database.

This script works because tempdb is rebuilt every time the SQL Server 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 model
go

/* 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 int
select @i = 1
while (@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+1
end
go