Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

dropping temdb's segment on master

4 posts in Windows NT Last posting was on 2000-07-04 12:57:51.0Z
Roger Pederson Posted on 2000-07-03 15:12:08.0Z
Message-ID: <3960AD48.E3683F18@oeamail.umaryland.edu>
Date: Mon, 03 Jul 2000 11:12:08 -0400
From: Roger Pederson <RPederson@oeamail.umaryland.edu>
X-Mailer: Mozilla 4.72 [en] (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: dropping temdb's segment on master
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 20
NNTP-Posting-Host: 134.192.186.14
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1948
Article PK: 1089400

We are in the process of setting up a Sybase Adataptive Server using
11.9.2 and have found conflicting information on the ability to remove
the tempdb area from the master device. One Sybase book suggest that
this should be done, and to use the following commands:

sp_dropsegment "default",tempdb,master
sp_dropsegment logsegement, tempdb, master

While in a performance and tuning class we were told that this was not
only impossible, but a simply evil thing to do.

The reason for wanting to do so is strictly performance. We have
created a new device for tempdb (2GB)on a seperate physical disk and we
don't want the server to be constantly going back and forth between the
segment on master and the one we just created.

any advice?


David Owen Posted on 2000-07-04 12:54:03.0Z
Sender: dowen@kashka.midsomer.org
Subject: Re: dropping temdb's segment on master
References: <3960AD48.E3683F18@oeamail.umaryland.edu>
From: David Owen <dowen@midsomer.org>
Organization: Midsomer Consultants Inc.
Date: 04 Jul 2000 06:54:03 -0600
Message-ID: <m2hfa63vkk.fsf@kashka.midsomer.org>
Lines: 30
User-Agent: Gnus/5.0802 (Gnus v5.8.2) Emacs/20.5
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: 24.70.23.180
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1944
Article PK: 1089393

>>>>> "Roger" == Roger Pederson <RPederson@oeamail.umaryland.edu> writes:

Roger> We are in the process of setting up a Sybase Adataptive Server
Roger> using 11.9.2 and have found conflicting information on the
Roger> ability to remove the tempdb area from the master device. One
Roger> Sybase book suggest that this should be done, and to use the
Roger> following commands:

Roger> sp_dropsegment "default",tempdb,master sp_dropsegment
Roger> logsegement, tempdb, master

Roger> While in a performance and tuning class we were told that this
Roger> was not only impossible, but a simply evil thing to do.

Are you positive that they were not talking about about deleting the actual
devive allocation from sysusages? There *used* to be a comment in the FAQ
about how to do this, and was generally part of the folklore of being a
Sybase DBA. However, this did turn out to be a truly evil thing to do
since Sybase could not help with recovery if you got into a major pickle.

If you do not like this idea, then there is an alternative in the FAQ,
whereby you fill the first 2 meg with junk. See my signature for a
reference.

I would drop the system segment too, and have done so in many different
environments.

--
David Owen Midsomer Consultants Inc. dowen@midsomer.org
Maintainer of the Sybase FAQ: http://www.isug.com/Sybase_FAQ


Andy Price Posted on 2000-07-03 21:29:42.0Z
From: "Andy Price" <andy.price@canada.com>
References: <3960AD48.E3683F18@oeamail.umaryland.edu>
Subject: Re: dropping temdb's segment on master
Date: Mon, 3 Jul 2000 22:29:42 +0100
Lines: 59
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6700
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700
Message-ID: <9BCPybT5$GA.272@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: host213-1-140-151.btinternet.com 213.1.140.151
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1947
Article PK: 1089396

Roger,

If you drop the segments from master, assuming you've extended tempdb to
another device, then all subsequent write activity will be performed on the
new device. You may also drop the system segment from master. After
recycling ASE, all reads and writes will be performed on your extended
device(s). Unlike the remarks in your P&T class, this is not an evil thing
to do.

For example,

-- extend tempdb onto a new device first, so that the segments on master can
be dropped later
alter database tempdb
on Tempdb3 = 600
go

-- drop initial segments from master, preventing any further writes to the
master device for tempdb
use tempdb
go
exec sp_dropsegment system, tempdb, master
exec sp_dropsegment "default", tempdb, master
exec sp_dropsegment logsegment, tempdb, master
use master
go

Hope that helps.

Andy

--
Andy Price
Sybase Certified Adaptive Server DBA - Associate v12.0 & v11.5

"Roger Pederson" <RPederson@oeamail.umaryland.edu> wrote in message
news:3960AD48.E3683F18@oeamail.umaryland.edu...
> We are in the process of setting up a Sybase Adataptive Server using
> 11.9.2 and have found conflicting information on the ability to remove
> the tempdb area from the master device. One Sybase book suggest that
> this should be done, and to use the following commands:
>
> sp_dropsegment "default",tempdb,master
> sp_dropsegment logsegement, tempdb, master
>
> While in a performance and tuning class we were told that this was not
> only impossible, but a simply evil thing to do.
>
> The reason for wanting to do so is strictly performance. We have
> created a new device for tempdb (2GB)on a seperate physical disk and we
> don't want the server to be constantly going back and forth between the
> segment on master and the one we just created.
>
> any advice?
>


David Owen Posted on 2000-07-04 12:57:51.0Z
Sender: dowen@kashka.midsomer.org
Subject: Re: dropping temdb's segment on master
References: <3960AD48.E3683F18@oeamail.umaryland.edu> <9BCPybT5$GA.272@forums.sybase.com>
From: David Owen <dowen@midsomer.org>
Organization: Midsomer Consultants Inc.
Date: 04 Jul 2000 06:57:51 -0600
Message-ID: <m2d7ku3ve8.fsf@kashka.midsomer.org>
Lines: 22
User-Agent: Gnus/5.0802 (Gnus v5.8.2) Emacs/20.5
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: 24.70.23.180
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1943
Article PK: 1089389

>>>>> "Andy" == Andy Price <andy.price@canada.com> writes:

Andy> Roger, If you drop the segments from master, assuming you've
Andy> extended tempdb to another device, then all subsequent write
Andy> activity will be performed on the new device. You may also drop
Andy> the system segment from master. After recycling ASE, all reads
Andy> and writes will be performed on your extended device(s). Unlike

Dropping the segments will not cause data to actually move, so the pages
that were on the master device will remain on the master device. I know
that this is not a lot, but could include any "localisation" that is added
to model. Even after dropping *all* of the segments, there will still be a
little bit of activity on the master device.


--
David Owen Midsomer Consultants Inc. dowen@midsomer.org
Maintainer of the Sybase FAQ: http://www.isug.com/Sybase_FAQ