delete DATA File of running Sybase Database on the fly or only with necessary downtime?

[Former Member]

Hi Sybase Gurus,

i got one question about datafiles in my sybase database 16.0.02.05. As i can see and also already used i can extend my database with some new datafiles over dbacockpit. But is there also an opportunity to delete not necessary data files on the fly /online? Or is it only possible with adequate downtime?

Thanks in advance and best regards

[former_member188958 · Accepted solution]

Hi Robert,

There are a couple approaches. Do you want to reorganize the location of the storage without changing the size of the database, or remove the device because the database is larger than needed so you want to make it smaller?

If you want to change the location you can either

a) dump the database, drop the database, create new devices in the desired location, create database, load the dump. You can delete the devices from the OS as soon as the database is dropped. This approach does require downtime.

b) use disk mirror to create a mirror of the device in the desired location, break the mirror retaining the new device, drop the old device

If you are wanting to make the database smaller, ASE has an ALTER DATABASE SHRINK command, after shrinking the database off the device the device can be dropped

https://help.sap.com/viewer/3bdda6b0ffad441aab4fe51e4e876a19/16.0.3.6/en-US/a6c0014bbc2b10148c02d4e1...

This shrink command is very fast when the database doesn't actually have any tables allocated on the device. In general, as ASE usually allocates free space from the beginning of the database towards the end (ordered by logical page number), the last devices added to the database are more likely to be empty.

Unfortunately, the shrink command is very slow if there are tables with non-unique indexes containing many duplicate entries. See KBA 2467562. This can be avoided by dropping such indexes before doing the shrink - but dropping and rebuilding indexes may have to be limited to maintenance windows. Another option is to drop all segments from the device and run REORG REBUILD on all tables to force them to relocate off the device. REORG can be run on multiple tables in parallel and has the side benefit of defragmenting and compacting the tables. If you have the luxury of time before you need to drop the device, this method is attractive as after zeroing out the segmap, the REORGs can be run over a number of maintenance windows (weeks, perhaps) but will eventually result in a device with no allocations on it (well, reorg doesn't move LOB (TEXT, IMAGE, JAVA, XML) allocations, but SHRINK is efficient in relocating LOB data)

-bret

[Former Member]

Hi Bret,

i want to delete just one datafile because customer dropped one client in erp and now they want to reclaim the possible free diskspace on filesystem. i will give the shrink feature a try because i can not run a reorg over such a long time. thank you very much for your clarification ! helped alot for my understanding in sybase!

BR

rob

[Former Member]

Hi Bret,

thank you for your answering. When i try to drop a device the system shows me the message

Server 'XXX', Procedure 'sp_dropdevice', Line 131: Device is being used by a database. You can't drop it.

Is there a mechanism like maxdb when i try to drop the datafile on sybase? Or have i to do a manuall relocate of the objects in this datafile? Add more space seems like no problem in sybase but removing is quite annoying >.<

Best Regards

Robert

[former_member188958]

Hi Robert,

You can drop an unused device at any time, no need to wait for a maintenance window.

Prior to 16.0 SP02 PL04 and 16.0 PL03, dropping a device was a 2-step process, you first
ran sp_dropdevice to remove the device from the system catalogs, then had to delete the file
from the file system directly from the OS. More recently, sp_dropdevice has been enhanced to both
remove the system catalog entries and delete the external file.

Cheers,
-bret