SQL Server Performance

Cannot find index issue, rebuild index task

Discussion in 'SQL Server 2008 General DBA Questions' started by anitadba2010, Jan 18, 2011.

  1. anitadba2010 New Member

    Hi SQL Gurus-

    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
  2. anitadba2010 New Member

    I believe the issue was temporary, i ran the task reorganize index package by itself and it executed successfully. so the whole package should run successfully shcheduled to run once in a week
    Anita
  3. Luis Martin Moderator

    If you run integrity, consistence and so on, I'll agree with you about temprary.
  4. RamJaddu Member

    Are you using correct database while re-building this index?
    Do you have any maintenance plan / scheduled job which delete and re-creating this indexs?
  5. anitadba2010 New Member

    Yes Ram, reindex task is part of the maintanance plan and it selects all user databases and executes reindexing. no we dont have any other job which deletes and recreate index
    however as i mentioned in my last post issue was temporary and its resolved now. But i am not sure why the plan failed once as its been running successfully last 4 weeks
    Thanks
    Anita
  6. RamJaddu Member

    Hi Anita,
    Nice to hear that problem resolved...
    Did you see any errors in event logs during that time.
    Cheers
  7. satya Moderator

    As you are using maintenance plans to perform the optimization tasks, it uses the SSIS provider to perform and may be a busy time on server will cause this error to occur. As you say it is corrected now make sure to monitor the disk space on server where the data & log files are located including the MSDB database size settings.
  8. satendra New Member

    Hi All,

    I am also facing the similar issue with the same error message. When I have checked the index mentioned in the error message, It is missing.
    Please suggest..

Share This Page