TRANSACTION LOG FULL - DB2

[Former Member]

Hello,

We face this problem of tlog getting full on weekends when long running BW jobs run.

We are asked to increase the tlog size by the customer.

Should we increase any parameter size for this..or there is any other way of solving this problem.

Regards,

uday.

[divyanshu_srivastava3 · Accepted solution]

Hi Udaya,

You can increase the secondary logs at run time.

Note 1293475 - DB6: Transaction Log Full

Note 1308895 - DB6: File System for Transaction Log is Full

Note: 495297 - DB6: Monitoring transaction log

Also, please share your DB and FixPack version.

Divyanshu

[former_member618864]

Hello Uday,

As, per your problem you have to increase the (LOGFILSIZ) size is less. It seems to be Ok with Primary and secondary log files.

Increasing the (LOGFILSIZ)  partially depends on database size  as well as the loads trigger by BI Team. As, per the standard Environments if you have more 1 TB Database it is better to main the each log file size upto 500 mb.

Log file size (4KB)

(LOGFILSIZ) = 512000

As, stated by Kumar you have to maintain sufficient space in (log_dir) directories as well.

Regards,

Rajesh N

[former_member618864]

Hello Uday,

When BI team triggers huge number of  data loads transaction log gets full automatically in most Cases. My suggestion is to increase the  Number of primary log files rather than increasing secondary log files.

In certain cases identify the application id displayed in db2diag.log which is causing the tlog issue, try to  force the application id by using command.

db2 "force applications (APP ID)"

Please let me know the Number of primary, secondary  log files configured in your environment and db2 database version with fix pack level.

Regards,

Rajesh N

[Former Member]

Hello Divyanshu and Raj,

Thanks for the reply.

Here is some info on primary,secondary logs and DB info.

Log file size (4KB)                         (LOGFILSIZ) = 22000                      22000

Number of primary log files                (LOGPRIMARY) = 150                        150

Number of secondary log files               (LOGSECOND) = 104                        104

Database Connection Information

Database server        = DB2/AIX64 9.7.3

SQL authorization ID   = DB2SID

Local database alias   = SID

Thanks!

uday.

[former_member184280]

Hi Uday,

Current Log file size : 85 MB , which I guess is less.

You can try increasing it to 150MB - 200MB.

Log file size (4KB)                         (LOGFILSIZ) = 51200

Before increasing this you should check that there should be sufficient space in log directory.

150*200 ~ 30GB.

What is your database size ?

Regards

- Avi