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).
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
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
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.
Related Information
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg22006683