Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2011
    Posts
    10

    Question Unanswered: DB2--disk full for archival logs--app down

    All,

    I would like to know why my db is generating so many logs that the disk space allocated for archival logs is getting filled up. It has generated more than 4000 logs in 5 days and this is the second time we are facing with such a issue. I am posting the information below from the snapshot and db2pd.

    Commit statements attempted = 52232355
    Rollback statements attempted = 1
    Dynamic statements attempted = 52242605
    Static statements attempted = 34826171
    Failed statement operations = 1
    Select SQL statements executed = 17419349
    Xquery statements executed = 0
    Update/Insert/Delete statements executed = 34813596
    DDL statements executed = 0
    Inactive stmt history memory usage (bytes) = 0

    Internal automatic rebinds = 0
    Internal rows deleted = 0
    Internal rows inserted = 0
    Internal rows updated = 0
    Internal commits = 664
    Internal rollbacks = 0
    Internal rollbacks due to deadlock = 0
    Number of MDC table blocks pending cleanup = 0

    Rows deleted = 496
    Rows inserted = 497
    Rows updated = 34812086
    Rows selected = 17500725
    Rows read = 52347875
    Binds/precompiles attempted = 0

    Log space available to the database (Bytes)= 516194841
    Log space used by the database (Bytes) = 6045159
    Maximum secondary log space used (Bytes) = 0
    Maximum total log space used (Bytes) = 101372207
    Secondary logs allocated currently = 0
    Log pages read = 0
    Log read time (sec.ns) = 0.000000004
    Log pages written = 27990499
    Log write time (sec.ns) = 14991.000000004
    Number write log IOs = 27990365
    Number read log IOs = 0
    Number partial page log IOs = 27012357
    Number log buffer full = 0
    Log data found in buffer = 0
    Log to be redone for recovery (Bytes) = 69219752
    Log accounted for by dirty pages (Bytes) = 69219752


    Database Partition 0 -- Database ******* -- Active -- Up 0 days 00:52:47

    Logs:
    Current Log Number 6651
    Pages Written 1625
    Method 1 Archive Status n/a
    Method 1 Next Log to Archive n/a
    Method 1 First Failure n/a
    Method 2 Archive Status n/a
    Method 2 Next Log to Archive n/a
    Method 2 First Failure n/a
    Log Chain ID 0
    Current LSN 0x001961B61CDA

    Address StartLSN State Size Pages Filename
    0x07700000C8C7D2D8 0x00195E628000 0x00000000 4000 4000 S0006648.LOG
    0x07700000C8E5E9D8 0x00195F5C8000 0x00000000 4000 4000 S0006649.LOG
    0x07700000C8C7F118 0x001960568000 0x00000000 4000 4000 S0006650.LOG
    0x07700000C8F929D8 0x001961508000 0x00000000 4000 4000 S0006651.LOG
    0x07700000C02AFF38 0x0019624A8000 0x00000000 4000 4000 S0006652.LOG
    0x07700000C8F95558 0x001963448000 0x00000000 4000 4000 S0006653.LOG
    0x07700000C9064EF8 0x0019643E8000 0x00000000 4000 4000 S0006654.LOG
    0x07700000C9068178 0x001965388000 0x00000000 4000 4000 S0006655.LOG
    0x07700000C8D19958 0x001966328000 0x00000000 4000 4000 S0006656.LOG
    0x07700000C005EF78 0x0019672C8000 0x00000000 4000 4000 S0006657.LOG
    0x07700000C8D19BD8 0x001968268000 0x00000000 4000 4000 S0006658.LOG
    0x07700000C8D1A798 0x001969208000 0x00000000 4000 4000 S0006659.LOG

    Also I used a query which is posted in the forum and got the output as follows for log used and percentages in mb.

    LOG_USED_MEG 2
    LOG_SPACE_FREE_MEG 495
    PERCENT_USED 0
    MAX_LOG_USED_MEG 96
    MAX_SECUNDARY_USED_MEG 0
    SECONDARIES 0

  2. #2
    Join Date
    Mar 2011
    Posts
    10
    Please help me and i am a nebie to db2.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 writes data to the transaction log when data is changed (insert, update, delete, import, etc) or when DDL is submitted (create table, drop table, etc). How are we supposed to know what SQL has been submitted against your database?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    DB2 does not just generate log records by itself. You would need to look at what your applications were doing at the time.

    May be it is time to modify your backup/recovery strategy to deal with larger log volumes.

  5. #5
    Join Date
    Mar 2011
    Posts
    10
    How do i know what processes were running during that time and also how can i know which one is filling up the logs. I will provide any additional information needed.

    Please let me know the steps to approach this problem.

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by crrulez View Post
    How do i know what processes were running during that time and also how can i know which one is filling up the logs.
    One way is to collect:
    db2pd -d <db name> -log -app -tra -dyn

  7. #7
    Join Date
    Mar 2011
    Posts
    10
    I checked for everything and still the issue prevails. the db is only 600mb in size and it has generated 10gb of logs since 4am till 1024am this morning.

    Also can you please help me turn the event monitor on so that i can get some idea of what is filling up the space.

  8. #8
    Join Date
    Mar 2011
    Posts
    10
    Also we backup the db every ni8 and move the archive logs twice everyday.

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by crrulez View Post
    I checked for everything and still the issue prevails.

    What did you check? Did you collect and check db2pd info?

  10. #10
    Join Date
    Mar 2011
    Posts
    10
    I checked the db2pd and also did snapshot. But didnt find the root cause for this. When it happened today i was able to see an update statement running from a batch job and am sure that caused the problem. But the same batch jobs run in all the environments which are replica of this environment used as test/dev/uat and has never caused any problem even today. All the parameters for the dbm and db are same in all the env and log space is also same.

    We push the archive logs every day at midnight,400am and 600pm.

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Something must be different. Check the amount of log space being by this update statement in different environments (db2pd -tra). Is DDL/data exactly the same?

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    1) Looking in original post, many updates were done.

    Update/Insert/Delete statements executed = 34813596

    Rows updated = 34812086
    Although, the average length of log records(or row length) was not clear,
    if assumed 100 byte for average length of log record,
    total bytes logged would be about 7Gbyte(34.8M * 2 * 100).

    So, I thought that logged 10Gbyte was not so unusually big.

    2)
    Commit statements attempted = 52232355

    Select SQL statements executed = 17419349

    Update/Insert/Delete statements executed = 34813596

    Rows deleted = 496
    Rows inserted = 497
    Rows updated = 34812086
    Rows selected = 17500725
    Compared those numbers, simple proportions could be seen.

    (Commit stmts) : (Select stmts) : (U/I/D stmts) = 3 : 1 : 2
    (Select stmts) : (Rows selected) = 1 : 1
    (U/I/D stmts) : (Rows updated) = 1 : 1

    These simple proportional relations made me thought of repeatedly execution of specific programs/transactions.

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    3) If guesses in 2) was sound,
    you might want to see something like called administrative report or service level report, so on.
    In those reports, you may find like top nn programs/transactions executed and/or heavy programs/transactions executed.

  14. #14
    Join Date
    Mar 2011
    Posts
    10
    Thank You all for posting your quick replies. As asked the log spaces and the data and ddl are same in all the environments.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •