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