Post reply

Cannot find index issue, rebuild index task

  • January 18, 2011 at 2:37 pm

    #230502

    Hi All-

    While running weekly maintenance plan the reorganizing the index task fails with below error.

    Executing the query "ALTER INDEX [IX_HealthAvgData_Branch] ON [dbo].[He..." failed with the following error: "Cannot find index 'IX_HealthAvgData_Branch'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    I generated the T-sql script for this task and found the below query within the script referencing the above missing index from the error msg

    USE [WarehouseReporting]

    GO

    ALTER INDEX [IX_HealthAvgData_Branch] ON [dbo].[HealthAvgData] REBUILD PARTITION

    = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS =

    ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )

    I verified manually that the index 'IX_HealthAvgData_Branch' does exist in the database and table referenced in the above query

    Than i ran the above alter index query in SSMS and it worked fine and than I ran a execute t-sql task using above query and it was successfull too.

    Any ideas what am i missing here, i am running out of ideas and hence need your suggestions 🙂

    Also verified index exists using query SELECT * FROM sys.indexes WHERE name = 'IX_HealthAvgData_Branch' and results successfully with the index

    (This plan consists of other tasks as well such as backup, update statistics, clean up etc)

    Thanks

    Anita

  • January 18, 2011 at 10:04 pm

    #1275094

    Was the index being dropped when the Maintenance Job was running? Did you try running the Maintenance Job again?

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply