Skip to Content

With the advent of ASE 15.7 is added a new feature for everyone and is expected to reduce the size of the Transaction Log to a database, this is known as Shrink Log Space, allowing to reduce the size of a database in the Log no need to rebuild (delete and create) the database.

Now with ASE 15.7 SP100 can reduce the size of a database of data on their devices, freeing up unused space to be reused or removed, this is true!

In general, you can reduce the size of databases that are online and in use. However, in some situations, Adaptive Server must be set in single user mode. See “Some Restrictions”.

All this can be done by running the command “alter database”.

With the option “alter database log off” we can reduce the size of the transaction log of a database.

And with the option “alter database off” can reduce the size of device data in a database.

To do this consider the following example:

Remove a data device to a database:

1> select @@version

2> go

———————————————————————————————————————————-

Adaptive Server Enterprise/15.7/EBF 21337 SMP SP101 /P/RS6000/AIX 6.1/ase157sp101/3439/64-bit/FBO/Thu Jun  6 16:46:11 2013                                                                                                                                    

1> sp_helpdb bac_datos

2> go


  name      db_size       owner dbid created      durability lobcomplvl inrowlen status                     

  ——— ————- —– —- ———— ———- ———- ——– —————————-

  bac_datos     7168.0 MB sa       5 Sep 05, 2013 full                0     NULL select into/bulkcopy/pllsort

  device_fragments               size          usage                created                   free kbytes    

  —————————— ————- ——————– ————————- —————-

  bac_datos01_dat                    4096.0 MB data only            Sep  5 2013  5:22PM                4175348

  bac_datos01_log                    1024.0 MB log only             Sep  5 2013  5:22PM       not applicable 

  bac_datos02_dat                    2048.0 MB data only            Sep  6 2013  9:26AM                2088960

                                                                                                               

  ————————————————————————————————————–

  log only free kbytes = 917286

We proceed to remove the device “bac_datos02_dat” if this device had written data pages, ASE proceeds to move these pages in the data device that contains sufficient space to receive them.

1> alter database bac_datos off bac_datos02_dat=’2048M’

2> go

ALTER DATABASE is starting in database ‘bac_datos’. This command will process 4096 allocation units.

ALTER DATABASE has finished in database ‘bac_datos’. This command processed 4096 allocation units of 4096 requested.


We check the status of the database:

1> sp_helpdb bac_datos

2> go

  name      db_size       owner dbid created      durability lobcomplvl inrowlen status                     

  ——— ————- —– —- ———— ———- ———- ——– —————————-

  bac_datos     5120.0 MB sa       5 Sep 05, 2013 full                0     NULL select into/bulkcopy/pllsort

  device_fragments               size          usage                created                   free kbytes    

  —————————— ————- ——————– ————————- —————-

  bac_datos01_dat                    4096.0 MB data only            Sep  5 2013  5:22PM                4175348

  bac_datos01_log                    1024.0 MB log only             Sep  5 2013  5:22PM       not applicable 

                                                                                                               

  ————————————————————————————————————–

  log only free kbytes = 917286, data only unavailable kbytes = 2097152

Consider columns “db_size” and “device_fragments”.

Some Restrictions:

* You can not reduce the databases master, model, archive, proxy, temporary, inmemory or other databases with reduced duration.

* You can not be include the parameter off in conjunction with the parameters log on, on the alter database command.

* You can not shrink a database while they are running commands as dump database, dump transaction or other alter database command.

* Must activate single user mode so long as you are trying to reduce a database on the number of pages a device. (This is part of the command syntax off alter database using the parameters from and to).

For more information see the technical documentation:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00641.1570100/doc/html/car1346282754166.html

I appreciate any help or correction to this information!

See you at the next ..

To report this post you need to login first.

4 Comments

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

  1. C. Outsourcing

    Hello,

    I tried to shrink logspace from database as per below steps:

    (log_001 was initially 10GB and tried to shrink 5GB. )

    Command :  Alter database <SID> log off log_001=”5G”

    After above command, I can see %GB free on logdevice using sp_helpdevice command.

    Question : How to release that 5GB space at OS level ?

    After some time, i atried to add same space to database again using command :

    Command :  Alter database <SID> log on log_001=”5G”

    This command added logspace in database, but I can’t see this under sp_helpdb free log space.

    Do you have any idea on this ?

    – Akash

    (0) 
    1. Giovanni Blanco Post author

      Hello!

      To answer your question:

      For starter, I would not try to recover the space of OS level, hence this wont guarantee data integrity.

      As a workaround I recommend to do a shrink as you did in ASE. Afterwards,  create on the OS a device of the desired size and then use the method of mirroring devices to dislodge the small device, to finally leave the new device with the desired new size.

      I hope I didn’t confuse you.

      We are here to help.

      Regards,

      Giovanni

      (0) 

Leave a Reply