ASE Shrinking Database Requirement.

[former_member463993]

Can any one suggest me . How Shrinking database can be done in ASE 15.7.

When should I decide database need to be Shrinked in ERP production SERVER ?

In earlier days in MS-SQLSERVER we used to  truncate log file before shrnking the data file.

And it will reduce the db file size and gain db performance.

[hans-juergen_schwindke · Accepted solution]

Hi,

please make sure that shrinking is supported. When it was released with 15.7, SP100, shrinking of Business Suite / Netweaver systems was not supported.

You can find a documentation on shrinking here: 

Shrinking Databases - System Administration Guide: Volume 2 - SAP Library

The main command is alter database .. off

I would shrink a database only if I gain a lot disk space.

From operational perspective a too large database does not matter too much. Backup does not take longer and is not bigger. Maintenance jobs work on the objects, not on un-allocated space.

A disadvantage of a larger database is that restoring this database from dump will take longer. During load the data is restored and the free space in the database is initialized. This takes longer in a larger database.

Best regards,

Juergen

[Former Member]

Sambhu,

Bret's link points to a procedure that is not for the faint of heart, and requieres skill and a great deal of caution.

If you don't have a version of ASE that supports device shrinking, then the best way to shrink a database still is:

1. Exract all database object, index, constraint, user and permission DDL

2. Extract all table data with the BCP out

3. Dump the database just in case

4. Drop the database

5. Re-create the database with devices to suit the desired size

6. Import all database object, constraint, user and permission DDL

7. Reload all table data using BCP in

8. Re-create all the indexes

While more laborious, this approach will recover disk space, degrament your daabase, and update index statistics. It will also be a good exercize to determine how well you are keeping track of your DDL, and a good way to estimate the time to rebuild in case of an absolute catastrophic failure.

I hope this helps.

Regards,

Jean-Pierre

[former_member463993]

Jean ,

           Thanks for your heart-throb  alternate method of shrink database.

I think it is last alternative path of shrinking.

[former_member89972]

Caveat :

Need replication server and enough storage at least during transition 

Solution :

You can replicate to a smaller sized database.

When replicated data is tested and good,  drop replication + old database completely.

Create new database and load the data from backup of replicate.

Benefits :

Down time could be near zero or up to dump & load of new smaller database.

Avinash

[Former Member]

Sambhu,

You are welcome! Obviously, it is more convenient to:

1) Use built in commands to shrink the database (quick and painless, or so they say)

2) Use Bret´s bits and bytes approach (we all like to feel macho and brag after doint it)

3) Replicate into a smaller database (although if you don´t already have RSyou have to buy it. $$$)

4) Use the logical rebuild. Laborious but with the performance benefits I indicated.

Regards,

Jean-Pierre

[former_member463993]

Jean ,

           I understand.And Noted your suggestion Thanks.

[former_member188958]

Here is a method off the ISUG FAQ site that was used before the ALTER DATABASE OFF command was developed:

Sybase FAQ §1.2: User Database Administration