Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error in Index Reorg in Maintenance Task Expand / Collapse
Author
Message
Posted Monday, November 8, 2010 10:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 16, 2014 10:44 AM
Points: 153, Visits: 583
Getting following error in Reorganize Index user DB Maintenance Task.

Error number: -1073548784

Executing the query "ALTER INDEX [REGAB_ACCOUNT_IX] ON [dbo].[RMARTRPT_DLY_REGAB_ACCOUNT_RPT] REORGANIZE WITH ( LOB_COMPACTION = ON )
" failed with the following error: "Cannot perform the specified operation on disabled index 'REGAB_ACCOUNT_IX' on table 'dbo.RMARTRPT_DLY_REGAB_ACCOUNT_RPT'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

what are the possible solutions to troubleshoot.
Post #1017301
Posted Monday, November 8, 2010 10:36 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 12:57 PM
Points: 576, Visits: 692
Re-enable, drop, or skip that index.
Post #1017317
Posted Monday, November 8, 2010 11:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 16, 2014 10:44 AM
Points: 153, Visits: 583
How do I skip it from the daily maintenance job?
I don't see enable option on the index. should i drop and recreate the index.
Post #1017340
Posted Monday, November 8, 2010 11:30 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
As the error message states that particular index is disabled. Rebuild it manually to enable it or drop it if that index is not required.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Post #1017359
Posted Monday, November 8, 2010 11:34 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 12:57 PM
Points: 576, Visits: 692
To re-enable a disabled index, you have to rebuild it using ALTER INDEX REBUILD etc, or just drop it completely (DROP INDEX tablename.indexname)

The reason for needing to rebuild it is because while it's disabled, the engine doesn't use it at all, so there are no data updates to it. Really the only reason to disable an index at all is so you can keep the schema in tact, and recreate it easily later if needed.
Post #1017363
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse