cancel
Showing results for 
Search instead for 
Did you mean: 

How to Increase the Transaction Log size?

Former Member
0 Kudos

Hi,

Currently, we are having problems with our transaction log due to its size which quickly get full. Our transaction log size is only 1024 MB or 1 GB and we usually dump the transaction log just to free up the space. How do we increase the size of our transaction log?

I'm new on this sybase database.

Thanks,

Joven

Accepted Solutions (1)

Accepted Solutions (1)

former_member188958
Active Contributor

Hi Joven,

You would use the ALTER DATABASE command.

alter database <dbname> log on <logicalDeviceName> = <size>

Example:

alter database mydb log on mylogdev = "100M"

go

If you don't have a suitable device with enough free space on it handy, you would either create a new one using the DISK INIT command, or enlarge an existing one using the DISK RESIZE command.  In both cases, you would want to coordinate with your OS system administrator to ensure the OS had the space available.  (This can be a very quick conversation if you are also the OS SA).

Cheers,

-bret

Former Member
0 Kudos

Hi Bret,

I have a user in our server OS which is the sybase database owner. Also I have checked the filesystem where our transaction log is located and has enough free space to extend the transaction log.

How does DISK RESIZE work? I think I just need to resize our transaction log to a bigger size.

Thanks,

Joven

former_member187136
Contributor
0 Kudos

Joven,

Its simple

1>disk resize

2>name = "<Device_Name",

3>size = "XXXM"

4>go

Example:

1>disk resize

2>name = "ABC_Data_001",

3>size = "1024M"
4>go

Where XXX denotes the size which is need to increase provided you have the space in the mount point.

Usage:

The disk resize command allows you to dynamically increase the size of your disks

After you resize a device, dump the master device, which maintains the size of the device in the sysdevices table. If you attempt a recovery from an old dump of the master device, the information stored in sysdevices will not be current.

Any properties that are set on the device continue to be set after you increase its size.

During the physical initialization of the disk, if any error occurs due to insufficient disk space, disk resize extends the database device to the point before the error occurs.

PS: Last point is very important

Regards

Kiran K Adharapuram

Former Member
0 Kudos

Hi Kiran,

Do I need to execute first the use master go before issuing the disk resize? Also after disk resize, do I need to alter database? Sorry I've got many questions since I'm new in sybase database.

Thanks,

Joven

former_member187136
Contributor
0 Kudos

Joven,

This is the sequence:

1> use master

1> disk resize name='<Device_Name>', size='1024M'

2> go

1> alter database <DB_Name> ON <Device_Name> ='1024M'

2> go


As example I gave 1024MB, you can increase accordingly,

also if you need to increase the log the disk resize remains same but there is change in alter:


1> alter database <DB_Name> log ON <Device_Name> ='1024M'

2> go


Regards

Kiran K Adharapuram

Former Member
0 Kudos

Hi Kiran,

I tried the steps you have mentioned and it increased the transaction log but now it is bigger than I wanted as it added the previous size with the size I issued. For example: my transaction log before is 10GB and I issued your command disk resize to 12GB, then it add up the 10GB I have so the total log size now is 22GB.

How can I adjust it to 12GB only? I just want my transaction log size to be 12GB.

Thanks,

Joven

former_member187136
Contributor
0 Kudos

Joven,

That is what the Sybase functionality is the value which you gave in size is the additional value what it will configure. Only the memory configuration(max memory, cache) which are done from isql has the functionality what you said above

Coming to your question:

There is a concept called Shrink the log/database:

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.


There is a beautiful blog:


Shrinking Log Space: SyBooks Online


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


Regards

Kiran K Adharapuram

Former Member
0 Kudos

Hi Kiran,

I have shrink my transaction log using the shrink log space you've mentioned. My transaction log size is now 12GB.

How do I check if the device for my transaction log is indeed 12GB. I have check the sp_helpdb <db_name> but it only shows the device fragments.

Thanks,

Joven

former_member187136
Contributor
0 Kudos

Hi Joven,

Check from

1>sp_helpdevice

2>go

Or

1>sp_helpdevice <device_name>

2>go

This commands shud be run from master database

1>use master

2>go

Regards

Kiran K Adharapuram

sladebe
Active Participant
0 Kudos

Re: How do I check if the device for my transaction log

In the database of interest, run: 

>  sp_helpsegment 'logsegment'

former_member187136
Contributor
0 Kudos

Correct Benjamin.

Is the space shrunk Jovan ?

Former Member
0 Kudos

Hi Kiran,

I have tried the sp_helpdevice <log_device_name> and found out that the physical disk is still 22GB. How do I shrink this to 12GB?

Thanks,

Joven

Former Member
0 Kudos

Hi Benjamin,

I have tried the sp_helpsegment logsegment and it shows that my transaction log is 12GB. But as I check on the filesystem the physical file is still 22GB.

Thanks,

Joven

former_member187136
Contributor
0 Kudos

Joven,

Provide me the steps which you followed so that I can verify the steps

Regards

Kiran K Adharapuram

Former Member
0 Kudos

Hi Kiran,

This is the steps I followed:

use master

go

disk resize name='DEV_log_001', size='10240M'

go

alter database DEV log ON DEV_log_001 ='10240M'

go

Thanks,

Joven

former_member187136
Contributor
0 Kudos

Jovan,

I asked you to provide the Shrink steps along with the below commands outputs please

sp_helpdb

go

sp_helpsegment "log segment"

go

sp_helpdevice

go

Regards

Kiran K Adharapuram

Former Member
0 Kudos

Hi Kiran,

Here is the steps I done when I shrink the log space:

alter database DEV log off DEV_log_001='10240M'



Here are the output of the commands you have sent to me:


name db_size owner                dbid             created durability lobcomplvl inrowlen status

---------------- ---------------------------------------------------- -------------------- ---------------- ------------------------------------------------ ---------------------------------------- ---------------------------------------- -------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

DEV                225280.0 MB sapsa                   4             Mar 26, 2015                                     full                                            100                                   2000                         ddl in tran, allow nulls by default, abort tran on log full, allow wide dol rows, deferred table allocation, page compression, allow incremental dumps, deallocate first text page

(1 row affected)

device_fragments               size          usage                created                   free kbytes

------------------------------ ------------- -------------------- ------------------------- ----------------

DEV_data_001                      51200.0 MB data only            Mar 26 2015  3:44PM                  31136

DEV_data_001                       2048.0 MB data only            Mar 26 2015  3:44PM                      0

DEV_data_002                      49152.0 MB data only            Mar 26 2015  3:44PM 24227680

DEV_data_002                       4096.0 MB data only            Mar 26 2015  3:44PM                4177920

DEV_data_003                      16384.0 MB data only            Mar 26 2015  3:44PM               16711680

DEV_log_001                        1024.0 MB log only             Mar 26 2015  3:44PM not applicable

DEV_data_003                      30720.0 MB data only            Mar 26 2015  3:44PM               31334400

DEV_data_003                       6144.0 MB data only            Mar 26 2015  3:44PM                6266880

DEV_data_004                      45056.0 MB data only            Mar 26 2015  3:44PM               45957120

DEV_log_001                        9216.0 MB log only             Mar 26 2015  3:44PM not applicable

DEV_data_004                       8192.0 MB data only            Mar 26 2015  3:44PM                8355840

DEV_log_001                        2048.0 MB log only             Mar 26 2015  3:44PM not applicable

                                                                                                                                                                                                                                                                                                                                               

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

log only free kbytes = 12508784

(return status = 0)

segment name status

---------------------------- ---------------------------------------- ------------------------

       2                      logsegment                                    0

device                                       size

-------------------------------------------- ------------------------------------

DEV_log_001                                  12288.0MB

Objects on segment 'logsegment':

table_name                               index_name                               indid                partition_name

---------------------------------------- ---------------------------------------- -------------------- --------------------------------------------------------

syslogs                                  syslogs                                      0                syslogs_8

Objects currently bound to segment 'logsegment':

table_name                               index_name                               indid

---------------------------------------- ---------------------------------------- --------------------

syslogs                                  syslogs                                      0

total_size        total_pages     free_pages      used_pages      reserved_pages

----------------- --------------- --------------- --------------- ---------------

12288.0MB         786432          781794          4638            0

(return status = 0)

device_name                                                              physical_name description status                   cntrltype                            vdevno                   vpn_low                      vpn_high

------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ ------------------------------------ ------------------------ ---------------------------- --------------------------------

DEV_data_001                                                             /sybase/DEV/sapdata1/DEV_data_001.dat file system device, special, dsync off, directio on, physical disk, 53248.00 MB, Free: 0.00 MB                                                                                                                                                                                                                                                                                                                                                    2                           0                                 5                         0                      27262975

DEV_data_002 /sybase/DEV/sapdata2/DEV_data_002.dat file system device, special, dsync off, directio on, physical disk, 53248.00 MB, Free: 0.00 MB 2                           0                                10                         0                      27262975

DEV_data_003 /sybase/DEV/sapdata3/DEV_data_003.dat file system device, special, dsync off, directio on, physical disk, 53248.00 MB, Free: 0.00 MB 2                           0                                11                         0                      27262975

DEV_data_004                                                             /sybase/DEV/sapdata4/DEV_data_004.dat file system device, special, dsync off, directio on, physical disk, 53248.00 MB, Free: 0.00 MB 2                           0                                12                         0                      27262975

DEV_log_001                                                              /sybase/DEV/saplog1/DEV_log_001.dat file system device, special, dsync off, directio on, physical disk, 12288.00 MB, Free: 0.00 MB 2                           0                                 6                         0                       6291455

master                                                                   /sybase/DEV/sybsystem/master.dat file system device, special, dsync on, directio off, default disk, physical disk, 400.00 MB, Free: 80.00 MB 3                           0                                 0                         0                        204799

saptempdb_data_001                                                       /sybase/DEV/saptemp/saptempdb_data_001.dat file system device, special, dsync off, directio on, physical disk, 4096.00 MB, Free: 0.00 MB 2                           0                                 9                         0                       2097151

saptools_data_001 /sybase/DEV/sapdiag/saptools_data_001.dat file system device, special, dsync off, directio on, physical disk, 4096.00 MB, Free: 0.00 MB                                                                                                                                                                                                                                                                                                                                                     2                           0                                 7                         0                       2097151

saptools_log_001 /sybase/DEV/sapdiag/saptools_log_001.dat file system device, special, dsync off, directio on, physical disk, 1024.00 MB, Free: 0.00 MB                                                                                                                                                                                                                                                                                                                                                     2                           0                                 8                         0                        524287

sybmgmtdev /sybase/DEV/sybsystem/sybmgmtdb.dat file system device, special, dsync off, directio on, physical disk, 150.00 MB, Free: 2.00 MB 2                           0                                 4                         0                         76799

sysprocsdev /sybase/DEV/sybsystem/sysprocs.dat file system device, special, dsync off, directio on, physical disk, 200.00 MB, Free: 0.00 MB 2                           0                                 1                         0                        102399

systemdbdev /sybase/DEV/sybsystem/sybsysdb.dat                                                                                              file system device, special, dsync off, directio on, physical disk, 24.00 MB, Free: 0.00 MB 2                           0                                 2                         0                         12287

tapedump1 /dev/nst0 unknown device type, disk, dump device 16                           2                                 0                         0                         20000

tapedump2 /dev/nst1 unknown device type, tape,        625 MB, dump device 16                           3                                 0                         0                         20000

tempdbdev /sybase/DEV/sybtemp/tempdbdev.dat file system device, special, dsync off, directio off, physical disk, 1024.00 MB, Free: 0.00 MB 2                           0                                 3                         0                        524287

(15 rows affected)

(return status = 0)

Thanks,

Joven

former_member187136
Contributor
0 Kudos

HI Joven,

This is the expected functionality

alter database DEV log off DEV_log_001='10240M'

The alter database command includes a log off parameter that removes unwanted portions of a database log, allowing you to shrink log space and free storage without re-creating the database


But the space is still be available in the device which you can re-use in the later point of time for altering database.


Cheers

Kiran K Adharapuram

Former Member
0 Kudos

Dear Kiran,

I am also new to SYBASE.

Can you please help me on below thread

BR,

Qazi Jamil

Answers (0)