Indexes being built multiple times in a single run

[Overcash]

This code seems to have a bug. If you dump the Defrag log, you will see that sometimes the Index Name and the SQL to rebuild or reorganize that index do not match. It turns out, that the SQL command to do the "fix" is from the last index that was determined to need a fix.
Here is SQL to see the problem. It extracts the index name from the Reindex/Reorg SQL command stored to the index that was being evaluated and dumps the ones that don't match:

SELECT TOP (1000) [indexDefrag_id]
,[databaseName]
,[objectName]
,[indexName]
,substring(substring(sqlstatement,CHARINDEX('[',sqlstatement) ,1000),1,CHARINDEX(']',substring(sqlstatement,CHARINDEX('[',sqlstatement) ,1000))) alteredIndexName
,[sqlStatement]
FROM [ITS_Maintenance].[dbo].[dba_indexDefragLog]
where
substring(substring(sqlstatement,CHARINDEX('[',sqlstatement) ,1000),1,CHARINDEX(']',substring(sqlstatement,CHARINDEX('[',sqlstatement) ,1000)))
<> indexName
order by indexDefrag_id desc

The bug seems occure when an index is being evaluated and turns out not to need a fix right now.

    Whenever it passes through this code:
        ELSE
            /* Print an error message if any indexes happen to not meet the criteria above */
            IF @printCommands = 1 OR @debugMode = 1
                RAISERROR('We are unable to defrag this index.', 0, 42) WITH NOWAIT;

If it passes through the code above, the @sqlcommand is never updated from the previous index in the loop, and contains the SQL for the last key that needed a fix. It then falls down into the EXECUTE code. That code has no logic to catch the situation and executes whatever is in @sqlcommand, which is not the current index being evaluated and had already been executed. It then logs that an index was fixed under the name of the current index with the SQL command for a different index.

In my database, several indexes are being rebuilt over and over again, a great waste of resources.

I put in a fix for my system. Inside the BEGIN-END block for the if @executeSQL = 1 I added a check to see if @sqlcommand <> ''. If emtpy then don't execute the SQL but do execute the status update. The same in the print only BEGIN-END block. After it leaves this nested IF and is ready to loop to the next index, I set @sqlcommand to empty.

This fix is not perfect because it puts a defrag date in the log for an index that was determined did not need it. But, you can tell in the log because the SQL command is empty.

My fix trying tonight (scan for RLO 09/24/2020 - not test yet, but will give you an idea of what I think is going on here):

        /* Are we executing the SQL?  IF so, do it */
        IF @executeSQL = 1 
        BEGIN
            -- RLO 09/24/2020 Added to prevent previous SQL command from being executed again if next index was determined not needing a fix
            if @sqlCommand <> ''  
			BEGIN                 -- RLO 09/24/2020 
	                SET @debugMessage = 'Executing: ' + @sqlCommand;
	                
	                /* Print the commands we're executing if specified to do so */
	                IF @printCommands = 1 OR @debugMode = 1
	                    RAISERROR(@debugMessage, 0, 42) WITH NOWAIT;
	
	                /* Grab the time for logging purposes */
	                SET @datetimestart  = GETDATE();
	
	                /* Log our actions */
	                INSERT INTO dbo.dba_indexDefragLog
	                (
	                      databaseID
	                    , databaseName
	                    , objectID
	                    , objectName
	                    , indexID
	                    , indexName
	                    , partitionNumber
	                    , fragmentation
	                    , page_count
	                    , DATETIMEStart
	                    , sqlStatement
	                )
	                SELECT
	                      @databaseID
	                    , @databaseName
	                    , @objectID
	                    , @objectName
	                    , @indexID
	                    , @indexName
	                    , @partitionNumber
	                    , @fragmentation
	                    , @pageCount
	                    , @datetimestart
	                    , @sqlCommand;
	
	                SET @indexDefrag_id = SCOPE_IDENTITY();
	
	                /* Wrap our execution attempt in a TRY/CATCH and log any errors that occur */
	                BEGIN TRY
	
	                    /* Execute our defrag! */
	                    EXECUTE sp_executesql @sqlCommand;
	                    SET @dateTimeEnd = GETDATE();
	                    
	                    /* Update our log with our completion time */
	                    UPDATE dbo.dba_indexDefragLog
	                    SET dateTimeEnd = @dateTimeEnd
	                        , durationSeconds = DATEDIFF(second, @datetimestart, @dateTimeEnd)
	                    WHERE indexDefrag_id = @indexDefrag_id;
	                END TRY
	                BEGIN CATCH
	
	                    /* Update our log with our error message */
	                    UPDATE dbo.dba_indexDefragLog
	                    SET dateTimeEnd = GETDATE()
	                        , durationSeconds = -1
	                        , errorMessage = ERROR_MESSAGE()
	                    WHERE indexDefrag_id = @indexDefrag_id;
	
	                    IF @debugMode = 1 
	                        RAISERROR('  An/error has occurred executing this command! Please review the dba_indexDefragLog table for details.'
	                            , 0, 42) WITH NOWAIT;
	
	                END CATCH
			END -- RLO 09/24/2020

            /* Just a little breather for the server */
            WAITFOR DELAY @defragDelay;

            UPDATE dbo.dba_indexDefragStatus
            SET defragDate = GETDATE()
                , printStatus = 1
            WHERE databaseID       = @databaseID
              AND objectID         = @objectID
              AND indexID          = @indexID
              AND partitionNumber  = @partitionNumber;

        END
        ELSE
        /* Looks like we're not executing, just printing the commands */
        BEGIN
            if @sqlCommand <> ''  --RLO 09/24/2020 
			BEGIN --RLO 09/24/2020 
					IF @debugMode = 1 RAISERROR('  Printing SQL statements...', 0, 42) WITH NOWAIT;
	                
	                IF @printCommands = 1 OR @debugMode = 1 
	                    PRINT ISNULL(@sqlCommand, 'error!');
			END --RLO 09/24/2020 

            UPDATE dbo.dba_indexDefragStatus
            SET printStatus = 1
            WHERE databaseID       = @databaseID
              AND objectID         = @objectID
              AND indexID          = @indexID
              AND partitionNumber  = @partitionNumber;
        END

	--RLO 09/24/2020  If index was determined not to be fixed, the code was exectuting the previous t_SQL again
		set @sqlCommand = '';

    END