Transaction log full

[#1]

Hi Team,

I am trying to update support package in our DEV system but i am getting trans log full error. Hence i took the translog backup, still i get the same error.

Can anyone tell where can i check the transaction log space because i could not find using helpdb. Also please provide commands to increase trans log space if possible.

Regards,

Adarsh

[Johan_sapbasis]

Hi,

It would be good to also configure a dump location with sp_config dump. You only need to change values in bold.

use master

go

Sybase configure dump

1> sp_config_dump @config_name='SIDDB',

2> @stripe_dir ='D:\Backup\Database',

3> @compression = '101',

4> @verify = 'header'

5> go

The change is completed. The option is dynamic and ASE need not be rebooted for

the change to take effect.

(return status = 0)

1> sp_config_dump @config_name='SIDLOG',

2> @stripe_dir ='B:\Log_Archives',

3> @compression = '101',

4> @verify = 'header'

5> go

The change is completed. The option is dynamic and ASE need not be rebooted for

the change to take effect.

(return status = 0)

Review settings:

1>use master

2>go

3>sp_config_dump

4>go

SIDDB

SIDLOG

sp_config_dump SIDDB

go

sp_config_dump SIDLOG

go

After this it is also easy to dump the transaction log, just remember that for your databases within your dataserver to be able to dump transaction log it has to be switched on with the following command.

> use master

2> go

1> sp_dboption SID, 'trunc. log on chkpt',false

2> go

Note the above command only enables the archiving of logs/truncating of logs for the database in bold. There is a shorter option of the command as well below.

sp_dboption SID, 'trunc', false

For more understanding refer to below link where Bret Halford's Profile | SCN explained it nicely.

https://scn.sap.com/thread/3286047

Below is an example of adding space by resizing the disk device for the saptools_log_001 by increasing the size in an increment of 1G additionally.

use master

go

disk resize name ='saptools_log_001' , size='1G'

go

Now you you need to allocate the new space of the disk to the database saptools so it can actually use the space this is where the alter database command comes in. Pay special attention to log on this is to be used only with log devices.

alter database saptools log on saptools_log_001 = '1G'

go

For normal data devices and database like data1 you would use the following. This increases SID_data_001 by 1G increment.

use master

go

disk resize name ='SID_data_001' , size='1G'

go

Now to allocate the space to the SIDDB for usage on device SID_data_001 you issue the following command see the log on has just changed to on otherwise you will be allocating mixed log and data segments which is not neat.

alter database SIDDB on SID_data_001 = '1G'

go

Also ensure all the sybase recommendations are set.

1539124 SYB: Database Configuration for SAP on Sybase ASE

1585981 SYB: Ensuring Recoverability for SAP Sybase ASE

1588316 SYB: Configure automatic database and log backups

1801984  SYB: Automated management of long running transactions

Specific to this log of yours.

1836607 SYB: How to solve error 1105 in database "saptools"

1989768 SYB: Error 1105 encountered against the log segment of the saptools database

1931223 SYB: SQL Error 1105 - ASE database data and/ or log segment is full

Kind Regards,

Johan

[hans-juergen_schwindke]

Hi,

for increasing the log device:

Please check whether you have free space available on your log device:

sp_helpdevice log_device_name

Please check the output for the word "Free".

If you don't have free space, you must add a new log device or increase the space of the existing log device. This is the command to increase:

disk resize name = 'log_device_name', size = 'additional size'

The additional size is specified like this for 500 MB: '500m'

If you have increased the size of the device or if you already had some free space on the device ("Free"), you can increase the transaction log using this command:

alter database databasename log on log_device_name = size_in_MB

----

Log truncation

You can't truncate the transaction log

a) if you have a long running transaction. Please check

select * from master..syslogshold

If you see a transaction that has been running for a long time, this will probably prevent you from truncating the log. One possibility would be to kill the transaction:

kill spid

b) if your log is too full. Then you can try:

dump transaction databasename with no_log

This does not write the transaction log to a file. So afterwards you can't execute dump transaction to a file anymore. First you have to do a full dump.

Best regards,

Juergen

[tilman_model-bosch]

All,

Never use dump tran with no_log.

If the log is full do this 

1) dump tran <DBNAME> to <dumpdestination>
2) if it did not free up space
     - check for idle long transactions that prevent log truncation - kill these.

     - rerun DUMP TRAN once the lonmg transactions have rolled back

3) In case you determine that the transaction log is too small , increase it .

In urgent cases DUMP TRAN ... WITH TRUNCATE_ONLY may get you out , but you need to take a full DB DUMP directly afterwards.

NO_LOG is  not recommended ! .

With kind regards
Tilman Model-Bosch