shrink database device

[Former Member]

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

[Former Member · Accepted solution]

Hello all,

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

Kind regards!

Thomas

[tilman_model-bosch · Accepted solution]

Dear Mr Hoerner ,

No shrinking devices is still not possible in ASE.

You have to remove all database fragments from a device and then drop it in order to free the space back to the file system.

I can see the point why you want to shrink devices, but currently this is not implemented.

The advice is to have several mid size devices and database fragments instead of having few large devices.

With kind regards

Tilman

[Former Member · Accepted solution]

Dear Mr Model-Bosch,

Thanks for your answer!

Is it possible to move the fragments to another device? Is this possible online?

Kind regards,

Thomas

[tilman_model-bosch · Accepted solution]

Dear Mr Hoerner,

It is possible . For fragments in the log segment use the "ALTER DATABASE ... LOG OFF" command (functionality supported in SAP environment).

For fragments ion the default, system or any data segment use the command "ALTER DATABASE ...OFF <DBDEVICE>" has been introduced new in 15.7 SP100. (Shrink database feature)

For SAP Netweaver databases the new 'shrink database feature' is not yet supported - due to limited testing. But it is planned to support that feature for future ASE releases for SAP Business Suite database as well.

For more information I refer you to  the New Features Guide for 15.7. SP100 .

With kind regards

Tilman Model-Bosch

[Former Member · Accepted solution]

Thank you very much!

[tilman_model-bosch · Accepted solution]

Dear Mr Hoerner,

Shrinking an ASE device is not possible and not planned as a feature to my knowledge.

So if you want to return space occupied by ASE device files to the file system this is whart you have to do:

- remove all database fragments from the device

   (either by DROP DATABASE or "ALTER DATABASE .. OFF <DBDEVICE>" (see replies above)

- drop the device in ASE

- delete the file in the directory. (make sure you do this only after you tripple verified you are deleteing the correct empty device file ! )

So - when you create database devices in ASE it is perhaps better to have a moderate number of mid sized devices rather than few large devices for your databases.

With kind regards

Tilman Model-Bosch

[Former Member · Accepted solution]

Dear Mr. Model-Bosch,

is the "ALTER DATABASE ...OFF <DBDEVICE>" in the meanwhile supported for SAP Netweaver systems? We are on ASE 15.7 SP 110.

If it is not released for that version of ASE, is it released for ASE 15.7 SP 121 or ASE 16.0?

We are currently in a situation where we would really need it!

Kind regards,

Thomas Hoerner

[tilman_model-bosch · Accepted solution]

Dear Mr Hoerner

Shrink database is supported for SAP Netweaver systems if ASE is 15.7 SP110 or higher.

Further important information are available in SAP notes

1943890 - SYB: Release information for SAP Sybase ASE 15.7.0.110 with SAP Business Suite

and

2000203 - SYB: Release information for SAP ASE 15.7.0.121 with SAP Business Suite

(SAP System must be offline, full database backup must be performed after wards) 

HTH

Tilman Model-Bosch

[Former Member · Accepted solution]

Thank you very much for the fast reply!

[former_member612721]

Did anyone manage to shrink ASE devices and release the space back to disk ?

I am in single user mode, and saptools has a new device allocated I can't manage to take all the space from the device sybbaldrdev due to the below error:

1> alter database saptools off sybbaldrdev='7M'
2> go
Msg 5072, Level 16, State 1:
Server 'NIC', Line 1:
ALTER DATABASE cannot remove the first 256 pages of a database.

I'm now trying to deallocate device sybbaldrdev from database saptools, and allocate to the new device.

Shouldn't the DB rearrange the pages to use the new device automatically?

[former_member188958]

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.

[former_member724636]

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

[Former Member]

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.