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.

Moving tempdb

2 posts in General Discussion Last posting was on 2011-08-26 15:31:27.0Z
Pål F Posted on 2011-08-26 11:29:50.0Z
Sender: 5213.4e565bd4.1804289383@sybase.com
From: Pål F
Newsgroups: sybase.public.ase.general
Subject: Moving tempdb
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e5783ae.1244.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 26 Aug 2011 04:29:50 -0700
X-Trace: forums-1-dub 1314358190 10.22.241.41 (26 Aug 2011 04:29:50 -0700)
X-Original-Trace: 26 Aug 2011 04:29:50 -0700, 10.22.241.41
Lines: 9
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30504
Article PK: 72683

Hi
I have an ASE installation with tempdb on the master and
tempdbdev -devises.
I want to change this to only have tempdb on a third device
"tempdb_dev".

Is this possible?

-Pål F.


Bret Halford Posted on 2011-08-26 15:31:27.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:6.0) Gecko/20110812 Thunderbird/6.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Moving tempdb
References: <4e5783ae.1244.1681692777@sybase.com>
In-Reply-To: <4e5783ae.1244.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e57bc4f@forums-1-dub>
Date: 26 Aug 2011 08:31:27 -0700
X-Trace: forums-1-dub 1314372687 10.22.241.152 (26 Aug 2011 08:31:27 -0700)
X-Original-Trace: 26 Aug 2011 08:31:27 -0700, vip152.sybase.com
Lines: 40
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30505
Article PK: 72684


On 8/26/2011 5:29 AM, PÃ¥l F wrote:
> Hi
> I have an ASE installation with tempdb on the master and
> tempdbdev -devises.
> I want to change this to only have tempdb on a third device
> "tempdb_dev".
>
> Is this possible?
>
> -PÃ¥l F.

Yes. Not particularly recommended though (it can make recovery more
difficult in cases where you have to rebuild the master device).

But if you want to do it anyway, the way to do it is

-- be sure you have a current backup of master
-- in case something goes wrong
dump database master to <dump device>
go

sp_configure "allow updates", 1
go
-- create a new database of default size to become
-- the new tempdb
create database mytempdb on tempdbdevice
go
delete sysusages where dbid = 2
go
update sysusages set dbid = 2 where dbid = db_id("mytempdb")
go
delete sysdatabases where name = "mytempdb"
go
sp_configure "allow updates", 0
go
shutdown
go

-- reboot ASE and alter tempdb to the full size desired.