Tempdb and devices?

[Former Member]

Hello, I have a tempdb with a few devices, each device was created for  data or  log  as it is recomended to keep them separate. My problem now is I need to take out a few of those devices that the tempdb is atached too, to then move them to a tempdb2 thats going to be created.  I know that the database ihas to be dropped to then move the devices but ASE dosnt let me. Please help me my job depends on this.

thank you in advance and sorry i for my english

ps.  tempdb needs to stay on the server. Just to make it simple if i didn't make my self clear is  that i need to move the tempdb devices and move them to a tempdb2

[Former Member]

I tried Kimon method but it don't work , I get the following error, ------ you cannot shrink database 'tempdb' because the database either has reduced durability or is being used as a template---

[Former Member]

any other suggesting?

[kimon_moschandreou]

Hi,

You tried alter database...

You could either use the second (old) approach or check your system

Please send

select @@version

go

sp_helpdb tempdb

go

Regards,

Kimon

[Former Member]

Try the link posted by Kimon Moschandreou to delete from sysusages. On restart of the server, tempdb should be created on the remaining segment in sysusages.

[Former Member]

Adaptive Server Enterprise/15.7/EBF 21339 SMP SP101 /P/X64/Windows Server/ase157sp101/3439/64-bit/OPT/Thu Jun 06 12:11:05 2013

name,db_size,owner,dbid,created,durability,lobcomplvl,inrowlen,status

'tempdb','     106.0 MB','sa','   2','Feb 27, 2014','no_recovery','         0','    NULL','select into/bulkcopy/pllsort, trunc log on chkpt, mixed log and data, allow wide dol rows'

[madhvi_pai]

Hi Alex,

tempdb is a SAP ASE system database with initial allocations on the master device, hence it cannot be dropped. Please consider extending tempdb on new devices or else, creating an additional tempdb would be a viable option.

Thanks,

Madhvi

[Former Member]

I had tried to backup the database tempdb to delete it and recreate the tempdb to free the devices then assigned the devices but it wont let me/. any ideas please help

[kimon_moschandreou]

Hello,

If you are on 15.7 SP100 or newer yoy may shrink a database, check this (alter database syntax) SyBooks Online and this is an example

Example 11

Removes 50MB of database sales_db from device mylogdev:

alter database sales_db log off mylogdev='50M'

in earlier versions follow this SyBooks Online (Archive)

It is an extract from ASE 12.5 documentation but applies also to ASE 15

Regards,

Kimon

[Former Member]

will this free my devices from tempdb?

[former_member188958]

Yes, the methods suggested by Kimon will free your devices from tempdb (to the extent that you specify).