TempDB - To move or not to move off Master

I was doing some research in the Sybase technical docs and
run across this document that advised not to move tempdb off
of Master.  This is contrary to what was taught in class.
Here is an excerpt of the article saying why not to do this:
"Although it is possible to move tempdb off the master
device later for space considerations, this is not
advisable. Once tempdb is moved off the master device, it is
difficult and time-consuming to recover if a problem occurs
on the master device or the device to which tempdb is moved.
This is because the sysusages table, which tracks the
assignment of device space to databases, is modified when
you move tempdb off master; the changes are difficult to
reconcile during recovery, and require some non-standard and
undocumented steps. "

The question is, what is best practices amongst all of you
concerning tempdb?

Thanks!
0
Robert
4/8/2004 6:03:35 PM
sybase.ase.administration 7058 articles. 2 followers. Follow

4 Replies
1016 Views

Similar Articles

[PageSpeed] 3

I think what that means is you can drop all of tempdb's segments from the
master device, but don't try to manually remove the tempdb fragment that is
left on the master device.  Once you remove the segments from master you
will be left with a fragment on master that never has any activity.

RD



<Robert Pollard> wrote in message news:407585e7.419d.846930886@sybase.com...
> I was doing some research in the Sybase technical docs and
> run across this document that advised not to move tempdb off
> of Master.  This is contrary to what was taught in class.
> Here is an excerpt of the article saying why not to do this:
> "Although it is possible to move tempdb off the master
> device later for space considerations, this is not
> advisable. Once tempdb is moved off the master device, it is
> difficult and time-consuming to recover if a problem occurs
> on the master device or the device to which tempdb is moved.
> This is because the sysusages table, which tracks the
> assignment of device space to databases, is modified when
> you move tempdb off master; the changes are difficult to
> reconcile during recovery, and require some non-standard and
> undocumented steps. "
>
> The question is, what is best practices amongst all of you
> concerning tempdb?
>
> Thanks!


0
Ray
4/8/2004 6:57:26 PM
> I think what that means is you can drop all of tempdb's
> segments from the master device, but don't try to manually
> remove the tempdb fragment that is left on the master
> device.  Once you remove the segments from master you will
> be left with a fragment on master that never has any
> activity.
>
> RD

I didn't get that it was talking about trying to physically
remove tempdb from master.  Maybe that is what they are
saying.
This is what I'm doing according to the class docs:
alter database tempdb on tempdb_sys_dev = "80M"
go
alter database tempdb on tempdb_def_dev = "80M"
go
alter database tempdb on tempdb_log_dev = "80M"
go

use tempdb
go
sp_dropsegment 'default', tempdb, tempdb_sys_dev
go
sp_dropsegment 'logsegment', tempdb, tempdb_sys_dev
go
sp_dropsegment 'system', tempdb, tempdb_def_dev
go
sp_dropsegment 'logsegment', tempdb, tempdb_def_dev
go
sp_dropsegment 'system', tempdb, tempdb_log_dev
go
sp_dropsegment 'default', tempdb, tempdb_log_dev
go
sp_dropsegment 'system', tempdb, master
go
sp_dropsegment 'default', tempdb, master
go
sp_dropsegment 'logsegment', tempdb, master
go

Dropping the system, default and logsegment should cause no
further activity on master, correct?  Is this what they are
talking about?

Thanks!

>
>
> <Robert Pollard> wrote in message
> > news:407585e7.419d.846930886@sybase.com... I was doing
> > some research in the Sybase technical docs and run
> > across this document that advised not to move tempdb off
> > of Master.  This is contrary to what was taught in
> class. Here is an excerpt of the article saying why not to
> > do this: "Although it is possible to move tempdb off the
> > master device later for space considerations, this is
> > not advisable. Once tempdb is moved off the master
> > device, it is difficult and time-consuming to recover if
> > a problem occurs on the master device or the device to
> > which tempdb is moved. This is because the sysusages
> > table, which tracks the assignment of device space to
> > databases, is modified when you move tempdb off master;
> > the changes are difficult to reconcile during recovery,
> > and require some non-standard and undocumented steps. "
> >
> > The question is, what is best practices amongst all of
> > you concerning tempdb?
> >
> > Thanks!
>
>
0
Robert
4/8/2004 10:03:56 PM

Robert, Pollard wrote:
> 
> > I think what that means is you can drop all of tempdb's
> > segments from the master device, but don't try to manually
> > remove the tempdb fragment that is left on the master
> > device.  Once you remove the segments from master you will
> > be left with a fragment on master that never has any
> > activity.
> >
> > RD
> 
> I didn't get that it was talking about trying to physically
> remove tempdb from master.  Maybe that is what they are
> saying.
> This is what I'm doing according to the class docs:
> alter database tempdb on tempdb_sys_dev = "80M"
> go
> alter database tempdb on tempdb_def_dev = "80M"
> go
> alter database tempdb on tempdb_log_dev = "80M"
> go
> 
> use tempdb
> go
> sp_dropsegment 'default', tempdb, tempdb_sys_dev
> go
> sp_dropsegment 'logsegment', tempdb, tempdb_sys_dev
> go
> sp_dropsegment 'system', tempdb, tempdb_def_dev
> go
> sp_dropsegment 'logsegment', tempdb, tempdb_def_dev
> go
> sp_dropsegment 'system', tempdb, tempdb_log_dev
> go
> sp_dropsegment 'default', tempdb, tempdb_log_dev
> go
> sp_dropsegment 'system', tempdb, master
> go
> sp_dropsegment 'default', tempdb, master
> go
> sp_dropsegment 'logsegment', tempdb, master
> go
> 
> Dropping the system, default and logsegment should cause no
> further activity on master, correct?  Is this what they are
> talking about?
> 

dropping the segments will cause no further *allocation* on master.
There will still be disk activity as the existing system tables are
still on that fragment, and the free space on allocated extents will
continue to fill.

The caution is for completely  removing tempdb from the master
device, not just dropping the segments.  Completely removing tempdb
from master can complicate recovery in the case where the master device
fails.

-bret
0
Bret
4/9/2004 3:44:53 PM
>
>
> Robert, Pollard wrote:
> >
> > > I think what that means is you can drop all of
> > > tempdb's segments from the master device, but don't
> > > try to manually remove the tempdb fragment that is
> > > left on the master device.  Once you remove the
> > > segments from master you will be left with a fragment
> > > on master that never has any activity.
> > >
> > > RD
> >
> > I didn't get that it was talking about trying to
> > physically remove tempdb from master.  Maybe that is
> > what they are saying.
> > This is what I'm doing according to the class docs:
> > alter database tempdb on tempdb_sys_dev = "80M"
> > go
> > alter database tempdb on tempdb_def_dev = "80M"
> > go
> > alter database tempdb on tempdb_log_dev = "80M"
> > go
> >
> > use tempdb
> > go
> > sp_dropsegment 'default', tempdb, tempdb_sys_dev
> > go
> > sp_dropsegment 'logsegment', tempdb, tempdb_sys_dev
> > go
> > sp_dropsegment 'system', tempdb, tempdb_def_dev
> > go
> > sp_dropsegment 'logsegment', tempdb, tempdb_def_dev
> > go
> > sp_dropsegment 'system', tempdb, tempdb_log_dev
> > go
> > sp_dropsegment 'default', tempdb, tempdb_log_dev
> > go
> > sp_dropsegment 'system', tempdb, master
> > go
> > sp_dropsegment 'default', tempdb, master
> > go
> > sp_dropsegment 'logsegment', tempdb, master
> > go
> >
> > Dropping the system, default and logsegment should cause
> > no further activity on master, correct?  Is this what
> > they are talking about?
> >
>
> dropping the segments will cause no further *allocation*
> on master. There will still be disk activity as the
> existing system tables are still on that fragment, and the
> free space on allocated extents will continue to fill.
>
> The caution is for completely  removing tempdb from the
> master device, not just dropping the segments.  Completely
> removing tempdb from master can complicate recovery in the
> case where the master device fails.
>
> -bret
0
Robert
4/9/2004 5:28:17 PM
Reply:

Similar Artilces:

Move Up/Move Down?
Does anyone know how to implement a Move Up/Move Down capability in a datagrd? I believe the question would boil down to these questions:1. How do you allow the user to select a row in a datagrid?2. How do you identify that row in code?3. How do you swap the position of two rows (in the underlying datasource)?Thanks! Try this link http://msdn.microsoft.com/msdnmag/issues/05/03/CuttingEdge/default.aspxIt night helpGood luckGraeme Thanks. I'll check it out and let you know......

moving tempdb off the master device
Can there be any problem if I move the tempdb off the master device? Thanks, Deniz ATA This is discussed in the Sybase FAQ: http://reality.sgi.com/pablo/Sybase_FAQ/Q1.7.html Good Luck! -- ----------------------- Shawn Enderlin, DBA senderli@holidayco.com Holiday Companies ----------------------- Deniz Ata wrote: > Can there be any problem if I move the tempdb off the master device? > > Thanks, > Deniz ATA Take a risk. Remove the whole thing from the master device. I've used this script for years now, and it has never failed me. I...

move tempdb off master device
Hi all, I need to move tempdb off the master devices. What is the appropriate step to accomplish this? Any issues with this? thanks tony check out this link, which i will guide you http://www.isug.com/Sybase_FAQ/ASE/section1.1.html#1.1.3 anand Tony_Tran wrote: > Hi all, > I need to move tempdb off the master devices. What is the appropriate > step to accomplish this? Any issues with this? > > thanks > tony Why do you think you need to do this? Sybase tech support recommends leaving at least the original fragment on the master device; comp...

move tempdb off of master device
Hi, What are the segment commands to drop tempdb from the master device? I remember doing this in class but can't find it in the online docs. I have assigned filespace to tempdb and would like to move tempdb off the master device. Thx, rick_806 It's best to leave the small fragment of master device allocated to the tempdb database, though there's typically nothing wrong with disabling future space allocations to the master device fragment. In this case you could use sp_dropsegment to drop all 3 segments (default, system, logsegment) from the master device fragment....

Move up/Move down buttons?
Hi,I'm trying to implement move up and move down buttons for a datagrid. The idea is that the user selects a row of the datagrid, then presses either the move up or the move down button to move the row up or down 1 row in the grid. If anyone has any ideas about how to implement such a feature, I would be grateful to hear about it.thanks. Though I haven't tried this, it should work.  Bind the datagrid to a datatable.  When the user wants to move the selected row, create a new row at the desired place and then delete the old row from the datatable and rebind the datagrid.  Che...

Tempdb moved of master device
Extended tempdb onto a new device and removed the original 2mb of tempdb that was on the master device. Unfortunately new device I extended tempbdb onto is corrupt and server won't recover since no tempdb. I need to move tempdb back onto master device. How do you do this ? Regards Kully Singh Hi, If you are able to create a new database then do the following. 0. sp_configure "allow update",1 1. create a new database new_tempdb ( 2MB ) to a new device. 2. select name,dbid from sysdatabases where name = "new_tempdb" Say this is = 10 3. delete ...

moving tempdb from the master device
Can there be any problem if I move the tempdb of the master device? Thanks, Deniz ATA This is a multi-part message in MIME format. --------------F1053104DDF21CBD72795AC6 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Deniz Ata wrote: > Can there be any problem if I move the tempdb of the master device? > > Thanks, > > Deniz ATA Hi Deniz, It can make recovery a real pain. I recommend leaving it on the master device (it's only 2mb) and altering it on to another device. Preferrably a filesystem for better performance...

Avoid moving tempdb off the master device?
Reading page 310 of the "Adminstrator's Guide to Sybase ASE 12.5" by WordWare Publishing, it says that one should "avoid moving tempdb off the master device" ... and continues... "Once tempdb is moved off the master device, it is difficult and time-consuming to recover if a problem occurs on the master device or the device to which tempdb is moved." How so? .... and yet on page 34 - 35 of the same book, it shows an example of creating a device on /tmp. Granted that this is only an example, but the idea of the author there was to use swap for ...

How to move tempdb from master dat device
Can any body explain me how to seperate tempdb placement from master database. Output of sp_helpdb tempdb as follows: name db_size owner dbid created status ------------------------ ------------- ------------------------ ------ -------------- ------------------------------------------------------------------------------------------------------ tempdb 1002.0 MB sa 2 Mar 01, 2004 select into/bulkcopy/pllsort, trunc log on chkpt (0 rows affected) name ...

Moving tempdb from master dev to new dev
How can I do this move....??? Thanks Zul Azmi Ahmad wrote: > > Rabindranath Raj wrote: > > > > How can I do this move....??? > > Thanks > > yes bb Are you having trouble with the posting of notes? On Mon, 13 Apr 1998 12:31:04 +0800, in sybase.public.sqlserver.administration Rabindranath Raj <rabin@bbmb.com.my> wrote: >Zul Azmi Ahmad wrote: >> >> Rabindranath Raj wrote: >> > >> > How can I do this move....??? >> > Thanks >> >> yes >bb Rabindranath Raj wrote: ...

Moving tempdb
Hi, Is there any simple method, without re-install, to move tempdb from physical disk device to memory devices? Thanks in advance. Gabriel Mautino. PD: ASE 11.9.2.4 for SCO UnixWare 7. 1) Delete entries from the sysusages for tempdb devices (other than master).. 2) Create tempfs.. 3) Disk init and alter tempdb again.. My 2c Radhey Shyam Remember to bounce the server between steps 1 and 2. Step 1 is actually documented in more detail in the Troubleshooting guide - Chapter 2 Encyclopedia of tasks, Other Useful Tasks, How to Reduce the Size of tempdb. -- Roge...

Moving tempdb
Can anyone help me? I wrote to this forum last week and received a mixed reaction to my 'dilema'. I would appreciate more thoughts as I still think I should seperate my tempdb from the master device. I know Sybase recommend not doing this but the reason they give is for recovery, and as far as I see it, if master is corrupt and I have to rebuild it, then I'll have to re-create my databases anyway, unless I can restore from a clean backup. Either way, master would be back to it's original state at some point in the recovery period. (am I making sense, hope I'...

Move my Master?
I have a mix of nw 6 and 5.1 servers. I'm upgrading all the servers to eDirectory 8.7.3. I want move the Master of Root to a new server I'll be building. How do I do this? Thanks. Zak wrote: > I have a mix of nw 6 and 5.1 servers. I'm upgrading all the servers to > eDirectory 8.7.3. I want move the Master of Root to a new server I'll be > building. How do I do this? The easiest way is to put a replica of [Root] on the new server, wait for DS to complete the operation, and then change the replica type to Master. --Greg Zak, Once t...

Moving tempdb
I need to remove tempdb from its current location, and reinstall using less devices. Right now it is splattered over master, and two other devices. I just wanted to make sure that I was not going to do any permanent damage if I did it the following way: 1) backup master 2) delete sysusages where dbid=2 go delete sysdatabases where dbid=2 go create database tempdb on master=2, some_other_device=150 go 3)recycle server Alan Chase wrote: > > I need to remove tempdb from its current location, and reinstall using > less devices. Right now it is splattered ov...

Web resources about - TempDB - To move or not to move off Master - sybase.ase.administration

Microsoft SQL Server - Wikipedia, the free encyclopedia
Microsoft SQL Server is a relational database management system developed by Microsoft . As a database, it is a software product whose primary ...

SQL中的图树层次结构_开发/数据库_IT专家网 开发/数据库
数据库子站,提供数据库管理软件优质技术信息的领先在线资源,是数据库管理员、开发人员和负责决策制定的 IT 经理人至关重要的社区。我们提供具有特色的、全面的内容,包括专家答疑、讨论论坛、白皮书、原创的行业新闻、评论分析以及技术技巧等。还有众多国内外数据库专家定期为我们的网友在线答疑,让我们的沟通学习更为方便。 ...


An Interview With Ken Henderson About The Forthcoming Book SQL Server 2005 Practical Troubleshooting ...
I am a big fan of Ken Henderson’s books, I believe that every SQL Server developer should have a copy of his books. When I noticed on Amazon.com ...

Frequently Asked Questions About TempDB
The questions came fast and furious in one of my recent TempDB webcasts, so here’s the ones I wasn’t able to answer during the live session: ...

Controlling Transactions and Locks Part 4 : SQL 2005 Snapshot
The first three installments of this series applied to both SQL 2000 and SQL 2005. Part 4 introduces Snapshots, new in MSSQL 2005.

How to move databases between computers that are running SQL Server
Describes how to move databases between computers that are running SQL Server. Provides step-by-step information about the method.

Microsoft Press
MICROSOFT PRESS publishes on Microsoft tools & technologies, plus programming best practices. Our blog offers book info, links to ebooks, & other ...

In-memory OLTP Engine (Hekaton) in SQL Server 2014 CTP2
Today I am going to discuss the new SQL Server 2014 feature In-memory OLTP a.k.a. Hekaton a Greek word which means hundred or hundredfold. In-memory ...

Texas Memory Systems: Applications
SQL Server Databases Accelerate your transaction logs, temporary database (tempdb), indexes, and frequently accessed tables with a RamSan SSD ...

Resources last updated: 1/19/2016 6:28:09 AM