Hi,

In the script for the Standard Edition, the variable @partitionNumber is not declared which gives an error. Am I missing something?

Thanks, Sri

Msg 4145

When I execute the script, I get:
Msg 4145, Level 15, State 1, Procedure dba_indexDefrag_sp, Line 343 An expression of non-boolean type specified in a context where a condition is expected, near ';'.

Is it supposed to be run as-is, or are there any other items to update other than preferences for rebuild threshold?
-Emily

EDIT:
Nevermind, I found that in this line there was a non-breaking space between ) and ! that needed to be removed:

IF @fragmentation >= @rebuildThreshold And IsNull(@containsLOB, 0) != 1 And @partitionCount <= 1

I am excited to try this out! Thanks.

Question regarding the Index Maintenance process

Let's say I run the first query to find the databases that need indexes defragged. Let's say the query returns an Advnetureworks db in a dev environment with 99 defragmentation.

Once I run the second query on the Adventureworks db and it runs successfully, shouldn't the first query yield a different number other than 99 when I run it again?

Thanks, Matt

Rebuild vs Defrag on script

So if I'm understanding this, there is logic on this script that will decide, based on fragmentation, whether the index needs to be reorganized or rebuilt. This is great.

However, unless I'm missing something, there is nothing that does an update of the stats should the script chooses to reorganize an index vs rebuilding it. This seems like a vital missing piece.

Simply running an 'update stats' job after this would not be ideal, since it will negate the advantage of this script, which is targeting only those databases/indexes that are beyond a fragmentation threshold.

Ideally, once the scripts determines which indexes will be reorganized rather than rebuilt, it should update the stats of only those. Unfortunately I'm not experienced enough to incorporate this logic on the current script. It would be awesome if someone could though.