Removing a device from a database Sybase ASE 15.0.2

[former_member258323]

There is an extra device created on a second database and not needed anymore. The device is already in use on the first database. There is no data being used on this device by the second database. What is the best method to remove the device from the second database preferably without having to restart the database?

ASE server is 15.0.2. (does have replication)

There is a method available on more recent ASE 15.7 servers. Is there any workaround on ASE 15.0.2?

[former_member188958 · Accepted solution]

I don't think you can avoid a reboot of the database.

The easiest method would be to dump the second database, drop the second database, re-create the second database for load using another device in place of the device you want to remove from the second database, load the dump.

However, I suspect that you not only want to remove the device from the database, but also make the database smaller - which isn't quite the same thing. I wrote up a process for shrinking a database a couple decades ago, long before the formal ALTER DATABASE SHRINK feature was introduced. It is published on the old International Sybase Users Group FAQ site

http://www.isug.com/Sybase_FAQ/ASE/section1.2.html#1.2.12. I consider it safe as long as you are sure to take a fresh dump of the database before starting (so you can restore the starting point if anything goes wrong) and full dbccs (checkalloc and checkdb) run clean at the end.

-bret

[former_member258323 · Accepted solution]

Thanks for the response Bret. The intent is to remove the device only from the second database where it is not being used. (there is also some extra unused space). Ideally, it needs to be done without a reboot. It seems that I may have come across your article to shrink the database before..If you find any other options, kindly notify.

Regards

Aravind

[mail2s_shiva14]

Nevertheless, how old the OP & the last reply has been, for the benefit of the fraternity ...

Extra device(s) can be de-allocated ( not dropped meaning just removing it from association of the database) by SP_DROPSEGMENT. By this, the device get disassociated with the database but is available to ASE & see in SYSDEVICES.

First find out to which Segment the device is allocated/mapped to - default (data) or logsegment and accordingly update the below code.

EXEC sp_dboption TestDB, 'single', true
GO
EXEC TestDB..sp_dropsegment logsegment, TestDB, device_019

Later, same device can be reused implying it is not dropped.

ALTER DATABASE DB2 ON device_019='4096M'

-- Lonely Rogue

[Mark_A_Parsons]

Dropping a segment does *NOT* remove the device allocation from the database.

After dropping the segment sp_helpdb will show the device allocation still associated with the database; as long as a fragment of a disk is allocated to a database said fragment cannot be reused in another database.

[mail2s_shiva14]

Thank you iron_horse for commenting and correcting. I didn't try re-allocating the segment/device to a new DB. Will try and find it.