More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
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's devices physically from the master device. This can make recovery of the server impossible in case of a serious error and so it strongly recommended that 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 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 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 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
(Continued on next question...)
