cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Defragmentation Indexes takes long

We have a CAS with a "slow" database. Therefore we noticed our weekly db maintenance tasks are failing within the default time. We increased the timeout step-by-step and are now at 13h timeout. We noticed the following log messages:

T DB    18-04-01 01:00:02.589    Checking defragmentation statistics ...
T DB 18-04-01 01:13:13.393 Defragmentation statistics collected. Duration: 13:10.804.
T DB 18-04-01 01:13:34.917 Defragmentation. 1 of 73 done. Duration: 00:20.321, Delay increase 00:20.321

[...]

T DB 18-04-01 11:48:18.559 Defragmentation. 32 of 73 done. Duration: 10h 35:03.963, Delay increase 0-8:0-14.00-596
T DB 18-04-01 11:48:18.559 Leaving idx defragmentation. No time to defragment 41 tables...
T DB 18-04-01 11:48:18.559 Defragmentation done. Duration: 10h 48:15.970

Does anyone know why the task is ending before the timeout is reached? The whole DBWeeklyMaintenance Task is starting at 3 am server time (which is equal to 1 am log time) and has a timeout of 13h as well as the timeout for the defrag command which is 13h as well.

The other thing I noticed is that there is a daily job which should to defragmentation as well but in the logs it is mentioned

1 SCHED    18-04-04 19:00:00.011    Task                  Server task DefragIndexesOnChangedTables is running in new thread now.
1 SCHED 18-04-04 19:00:00.011 DefragIndexesOnChangedTables Start executing command DefragIndexesOnChangedTables from server task DefragIndexesOnChangedTables for 2018-04-04 21:00
T ADM 18-04-04 19:00:00.011 DefragIndexesOnChangedTables is switched off
1 SCHED 18-04-04 19:00:00.011 DefragIndexesOnChangedTables End of successful execution (0) of command DefragIndexesOnChangedTables from server task DefragIndexesOnChangedTables for 2018-04-04 21:00

The task file tasks-100-hcbs.xml looks fine for this task, is there any other option to (de-)activate this task?

Thanks for any input on this.

Best regards,

Friederike

2 REPLIES 2

BabarQayyum
Leader

Hello Friederike,

Most probably an other scheduled task within 13 hours interrupted the DBWeeklyMaintenance and also you can check with the DBA in case they are taking incremental/full backup at the same time which is causing this problem and slowing down the DBWeeklyMaintenance process.

How many raw sessions do you have because taking more than 7-8 hours for the weekly task should be the maximum?

DefragIndexesOnChangedTables task is only configurable in the tasks-100-hcbs.xml

Regards,

Babar

Hello Babar

we have a virtualized environment which is somehow not performing properly (we know this).

We have only around 3M Raw Sessions on this CAS - therefore it should be faster but it might be slower due to virtualization.

I'm only wondering why the Daily Task for DefragIndexesOnChangedTables is active in the tasks-100-hcbs.xml but in the logs it shows a message with "Task is switched off". There must be another configuration possibility for this task but I never noticed another one.

Best regards,

Friederike