Skip to Content

Hi guys and girls,

With this query coming up a lot and my recent experience in this I have decided to write this blog post. I would like for any improvement comments by all the SAP employees in this space area who are also a great support to everyone running sybase. And to all my peers.

SAP Official Stance (According to my knowledge)


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

Scenario

A finger fault caused me to add 18GB to a device instead of 1GB i.e. to grow from 17GB to 18 GB.

Prerequisites for fix

1. Other devices should have free space to accomadate used space in use by your erroneous device

2. Offtime to run alter database off command (database needs to be in single user mode)

3. Shrink database is supported for 1943890 – SYB: Release information for SAP Sybase ASE 15.7.0.110 and higher.

Solution

1. First check space usage on all your devices use sp_helpdevice device name i.e. sp_helpdevice SID_data_005 there should be free space on all device equivelant to space on device you want to reclaim/drop. Other option is DBACOCKPIT to check for freespace example below:

DBACockpit.jpg

2. Once you determined there is enough space, you need to STOP SAP, leave database running. And switch to single user mode you have to switch to master database and to single user mode

use master

go

sp_dboption SID, ‘single user’,true

go

3, If there are users still on the system run sp_who to see which user it is and kill the session number. In below example user sapsa i.e. sap is logged onto session 87 so you will run kill 87

KIll.JPG

4. Once you are in single user mode you have to take database fragments of device i.e. reallocate it to other devices. To do this you will run command as follows remember this requires SAP downtime so do it in small increments of maybe 2gb to 3gb as this does run for hours. Remember to use DB name SID and target device name this case SID_data_005. Always specify a unit specified i.e. M(Megabyte) or G(Gigabyte)

SID.JPG

5. Once this has completed check your device has no allocated fragments left so FREE space should match total space as per below example.

space.JPG

6. Now that the device space is free on this device you have to drop the device from the database.

SID.JPG

7. As per above example you can see the device is dropped but the device file exists on OS still and needs to be deleted to free up the space. Current free space is 26.3GB

D.JPG

Device size on disk below.

data.JPG

8. Once device is deleted as per below you can see the free space has now increased from 26.3GB to 68.3GB

data.JPG

9. Now you need to recreate device with exact same details as per before but with initial size that you need in my case 10GB.

disk init name=’SID_data_005′,physname=’D:\sybase\SID\sapdata_5\SID_data_005.dat’,size=’10G’

space.JPG

10. Now the device is created but you still need to allocate database space to device to have it to be usable and to allocate database fragments to the device

space1.JPG

space.JPG

11. Now that the device space has been recreated, and allocated you can check the free space of the Data drive has increased from 26.3GB initially to 58.3GB which matches the 30GB if space size decrease in device SID_data_005

space12.JPG

References

1.  SyBooks Online88.

2. SyBooks Online

3. https://websmp130.sap-ag.de/sap/support/notes/0001943890

Please feel free to comment on improvements or incorrect statements and errors.

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

    1. Nicolaas Johannes Van Zyl Post author

      Hi Kiran,

      Thanks for the comment I just want to ensure accuracy as I have only been involved in Sybase for a very short period 0f 2 months now.

      Thanks for the comment.

      Regards,

      Johan

      (0) 
  1. Rajeev Sharma

    Hello Nicolaas,

    There are very few wikis and blogs on SAP SYBASE ASE.

    You have tried to address the recurring common issue during restore.

    Thank you very much.

    Regards

    Anand

    (0) 
    1. Nicolaas Johannes Van Zyl Post author

      Hi Anand,

      Thank you very much for the positive comment if this blog just helped on person it makes me feel good. You are right there are not much information wiki related or how to by SCN peers.

      Have a good day.

      Johan

      (0) 
  2. Avinash Kothare

    Excellent work. 

    On earlier ASE versions this would  be a long operation. 

    Also when you expand a database typically there are implications for restores/loads.

    E.g. if you expand a production database you have to have a matching database size in other non-production (DEV, QA, UAT,  etc.) databases. 

    So Johan possibly avoided doing some additional work !!

    Cheers

    Avinash

    (0) 
    1. Nicolaas Johannes Van Zyl Post author

      Hi Avinash,

      At the time of me doing this wiki I only had 2 months experience on Sybase so it was a rag tag effort. But it did teach me a valuable lesson be sure of what you do before you do it.

      Have a nice day.

      Johan

      (0) 

Leave a Reply