Error in Index Reorg in Maintenance Task

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Error in Index Reorg in Maintenance Task

Error in Index Reorg in Maintenance Task

Rate Topic

Display Mode

Topic Options

Author

Message

Ranzz

Posted Monday, November 08, 2010 10:22 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 9:26 AM
Points: 138, Visits: 511

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

Derrick Smith

Posted Monday, November 08, 2010 10:36 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 2:06 PM
Points: 574, Visits: 685

Re-enable, drop, or skip that index.

Post #1017317

Ranzz

Posted Monday, November 08, 2010 11:14 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 9:26 AM
Points: 138, Visits: 511

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

Adiga

Posted Monday, November 08, 2010 11:30 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 4:09 AM
Points: 1,618, Visits: 20,904

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

Derrick Smith

Posted Monday, November 08, 2010 11:34 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 2:06 PM
Points: 574, Visits: 685

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 »

Permissions