Dropping device without dropping database first ...

[lavaltsang]

Hi,

I was wondering if there is a way to drop a device without
dropping the database first, the device is not associated
with any segment but when I issue sp_dropdevice ASE says
that the device is being used by a database. I have dig in
the document and so far it's seems I have to drop the
databse first and that's not an option.

Thanks in advance,
Laval.

[Rob Verschoor]

If you cannot drop the database (I'm assuming it would take too long if your
database is large), you could use 'dbcc dbrepair(..., dropdb)' (requires
patching the database status first), or just manually delete the rows for
that database from sysusages and sysdatabases, and then reboot ASE. Then you
should be able to drop the device. Needless to say, any data in that
database will be lost when following this procedure.

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase Replication Server Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"

mailto:r...@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

<Laval Tsang> wrote in message news:418bb27b.481...@sybase.com...

[Mark K]

Rob,
I *think* his point is that he can't drop the database because it is needed,
not due to a technical flaw.

Laval,
If you can't drop the device, ASE thinks it is needed for a database. You
need to be careful here in case it is actually being used. Let's start by
getting the output from sysdevices and sysusages.

Mark Kusma

"Rob Verschoor" <r...@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote in
message news:418bbd4c@forums-2-dub...

[Rob Verschoor]

Ah... yes, you could be right!

Rob V.

"Mark K" <xxxx> wrote in message news:418f6654$1@forums-1-dub...

[Bret Halford]

Is the intent to free up a particular device, or to shrink the
database to a smaller size?

If the former:
The device can be mirrored to a new device, then the mirror broken
retaining the secondary side, thus swapping the new device for the
old device

If the later:
http://www.isug.com/Sybase_FAQ/ASE/section1.2.html#1.2.12

-bret

[lavaltsang]

Thanks all for your response,

That's what my intention was, is to shrink the database to a
smaller size, after having originally messed around with the
device. Now the device is not assigned to any segment, so
this means it's not being used by any database but the
Sysdevice shows my device with low=318767104,
high=318818303,
status=2 and Sysusage shows a dbid, segmap=0,
vstart=318767104 and a good guess(i'll confirm after going
through the docs) is that it's still being used but it's not
assigned
to any segemnt as sp_helpdb would confirm.

Bottomline, what I am looking for is a way to reverse an
"Alter database xxx on new_device=##"

Thanks.

> > > > --- Rob Verschoor

[Bret Halford]

"Now the device is not assigned to any segment, so
this means it's not being used by any database"

Actually, it doesn't mean that. segments just control where
future allocations of objects go. If an object has been allocated
extents on some device fragment, it is not moved off that device
fragment when you drop the segment from the fragment.

-bret