IBM Support

Massive SQL Transaction Logs caused by rebuilding SQL indexes

Troubleshooting


Problem

Customer notices that their Controller database's transaction log file (LDF) is regularly getting exceptionally large.

Symptom

In one real-life customer example, the transaction log (LDF file) grew by approximately 100Gb in 24 hours.

Cause

In one real-life customer example, the cause was that:

  • The total size of the indexes (for the Controller database) was approximately 50Gb
  • The customer rebuilt the indexes twice in 24 hours:
    • The first rebuild was using the Controller GUI (inside a database optimise, the user ticked the option 'rebuild indexes')
    • The second rebuild was a few hours later, as part of a database maintenance plan (regular scheduled rebuild).
=> Therefore there was a total of 100Gb growth, purely because of rebuilding the SQL indexes (twice).

Diagnosing The Problem

To find out the size of the current indexes (or the largest database tables), run the following script:

    select name=object_schema_name(object_id) + '.' + object_name(object_id)
    , rows=sum(case when index_id < 2 then row_count else 0 end)
    , reserved_kb=8*sum(reserved_page_count)
    , data_kb=8*sum( case
    when index_id<2 then in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count
    else lob_used_page_count + row_overflow_used_page_count
    end )
    , index_kb=8*(sum(used_page_count)
    - sum( case
    when index_id<2 then in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count
    else lob_used_page_count + row_overflow_used_page_count
    end )
    )
    , unused_kb=8*sum(reserved_page_count-used_page_count)
    from sys.dm_db_partition_stats
    where object_id > 1024
    group by object_id
    order by
    rows desc

Example:

In one real-life customer example, the results showed:



If you add up the total of the indexes, their total size was approximately half the size of the entire data (mdf file).

For example (see above), some of the largest index sizes were:
  • xdbaudit = 7.3 Gb
  • xdbacclock = 1.7 Gb

Resolving The Problem

Fix:
Ensure that your SQL server's hard drive have enough free (spare) hard drive space, so that they can cope with the size of the transaction logs.

Workaround:
There are several possible methods to reduce the size of space consumed by transaction logs:

  • Method #1
Reduce the size of the database indexes, by reducing the sizes of the database tables.

Steps:
For more information on reducing the size of some of your database tables, see separate IBM Technotes such as: 1681197, 1624409 & 1696796.
  • Method #2
Ensure that your SQL database maintenance plan does not rebuild the indexes too frequently.

Example:
For example, make sure that you:
  • only rebuild your indexes once a week (not every day)
  • perform this task on a day when nobody else is likely to logon to Controller and perform a rebuild inside the GUI (via a database optimise), such as Sunday.

[{"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Controller","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.2.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2018

UID

swg22006683