Skip to Content
avatar image
Former Member

shrink database device

Hello all,

I know that with Sybase ASE 15.7 you can shrink log space with the following command (example for the saptools):

alter database saptools log off logsaptools_1 = '200M'

I have tried it out and it works fine.

Is there also a possibility to shrink the device logsaptools_1 afterwards?

Kind regards,

Thomas

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 21, 2013 at 03:11 PM

    Hello all,

    is it now possible with SP > 100 to shrink also the device after shrinking the DB?

    Kind regards!

    Thomas

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 18, 2013 at 04:40 PM

    The supported way to reduce the size of a device would be to dump all the databases that have allocations on that device, then drop all those databases, then drop the device (sp_dropdevice from the ASE side and, if using file system devices, an RM or DEL on the file from the OS side), then DISK INIT a new device of the desired (smaller) size, then CREATE DATABASE FOR LOAD for all the dropped databases, then LOAD DATABASE from the dump files for all the dropped databases.

    Might be better to just leave the free space around for future expansion.

    Add comment
    10|10000 characters needed characters exceeded

    • With the new(er) 'alter database/off' capabilities another option may be:

      - create new (smaller) device (eg, disk init logsaptools_2)

      - get list of databases that currently reside on logsaptools_1; note size of fragments

      - extend these databases onto the new device by the same amount of space currently allocated from logsaptools_1

      - run 'alter database/off' to remove logsaptools_1 from all databases

      - sp_dropdevice logsaptools_1

      - drop/remove logsaptools_1 at the OS level

  • avatar image
    Former Member
    Feb 18, 2013 at 03:28 PM

    Hi Thomas,

    You can't shrink a database device once it's created. If you want a smaller log device, you have to initialize a new one and then "log off" the bigger one.

    Best regards.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 18, 2013 at 03:39 PM

    It is not a good idea to update system catalog directly. Also in ISUG post , it is clearly mentioned that shrinking database with manual updates is not supported. Therefore , it is better not to use it under production systems , however you can always execute alter database log off

    -HTH

    Manish Negandhi

    Add comment
    10|10000 characters needed characters exceeded