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
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 18.104.22.168 and higher.
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:
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
sp_dboption SID, ‘single user’,true
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
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)
5. Once this has completed check your device has no allocated fragments left so FREE space should match total space as per below example.
6. Now that the device space is free on this device you have to drop the device from the database.
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
Device size on disk below.
8. Once device is deleted as per below you can see the free space has now increased from 26.3GB to 68.3GB
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’
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
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
Please feel free to comment on improvements or incorrect statements and errors.