Skip to Content

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Apr 24 at 06:29 PM

    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/a6c0014bbc2b10148c02d4e1d8d839f2.html


    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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 23 at 01:22 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 24 at 06:55 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 25 at 01:38 PM

    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

    Add comment
    10|10000 characters needed characters exceeded