indexDefrag went rogue

SQL Server Administration
1

Hi, my friends,

I have this next in a job, and it has been running fine. Typical duration of time is about 1 hour.

This last weekend, it went into a tail spin. After I canceled it, it had been running for 18 hours, and had causing other jobs to hang.

Any suggestion on where I should look for the root cause?

Also, what could be the reason(s) that the time limit of 3 hours here was not honored?

Thanks!

EXECUTE dbo.dba_indexDefrag_sp
@executeSQL = 1
, @printCommands = 1
, @debugMode = 1
, @printFragmentation = 1
, @forceRescan = 1
, @maxDopRestriction = 1
, @minPageCount = 8
, @maxPageCount = NULL
, @minFragmentation = 10 --pick the higher ones first, then down to 10
, @rebuildThreshold = 30
, @defragDelay = '00:00:05'
, @defragOrderColumn = 'page_count'
, @defragSortOrder = 'DESC'
, @excludeMaxPartition = 1
, @timeLimit = 180 --min = 3 hours
, @onlineRebuild = 0
/* 1 = online rebuild; 0 = offline rebuild; only in Enterprise */
, @database = 'mydb';

2

That's your own custom code. How on earth are we supposed to guess what that code contains, and what statement(s) in it caused the time limit to be exceeded?

3

Oh, sorry!

I got it from sqlfool.com and thought that is a well known one.

Never mind then.

Thanks anyway!

4

sqlfool.com says:
http://sqlfool.com/2011/06/index-defrag-script-v4-1/

@timeLimit = stop defragging after the specified amount of time [Minutes] has elapsed.
Please note, it will not kill a defrag that is currently in process, even if it exceeds the threshold.

So either the SProc was blocked for some reason (I can't think of one), or a single index was taking 18 hours (less 2:59 maybe ...) - that seems improbable too.

Could use

@executeSQL = 0

to output a series of SQL statements (instead of executing them) and then you could run that manually and see where it gets stuck

That said,

@printCommands =1
@debugMode = 1
@printFragmentation = 1

should have output some debugging info - what did they say?