Rebuild Index - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Rebuild Index

Author

Topic

DBA007
Posting Yak Master

145 Posts

Posted - 2010-01-14 : 11:04:18

My Maintenance plan fails for rebuilding indexes,the error message was
Executing the query "ALTER INDEX [IDX_POWER_ID] ON [dbo].[tblPower] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
" failed with the following error: "The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.tblPower' and the index name 'IDX_Power_ID'. The duplicate key value is (112, 5635894).
The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
any help greatly appreciated.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-14 : 11:22:36

That looks like a form of database corruption (assuming that's supposed to be a unique index).

Please run this and post the full output.


DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

--
Gail Shaw
SQL Server MVP

DBA007
Posting Yak Master

145 Posts

Posted - 2010-01-14 : 11:36:57

thanks Gila
this is the output i got by running DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

Msg 2508, Level 16, State 3, Line 1
The In-row data RSVD page count for object "tbl_NewOrder", index ID 0, partition ID 40992070500352, alloc unit ID 40992070500352 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.

CHECKDB found 0 allocation errors and 1 consistency errors in table 'tbl_NewOrder' (object ID 625489357).

Msg 2508, Level 16, State 3, Line 1
The In-row data RSVD page count for object "CRDDated", index ID 0, partition ID 60372450148352, alloc unit ID 60372450148352 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.

CHECKDB found 0 allocation errors and 1 consistency errors in table 'CRDDated' (object ID 921210482).

Msg 2508, Level 16, State 3, Line 1
The In-row data RSVD page count for object "ED_Bill", index ID 0, partition ID 61421029883904, alloc unit ID 61421029883904 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.

CHECKDB found 0 allocation errors and 1 consistency errors in table 'ED_Bill' (object ID 937210539).

Msg 2508, Level 16, State 3, Line 1
The In-row data RSVD page count for object "tbl_NewOrder", index ID 0, partition ID 87950354808832, alloc unit ID 87950354808832 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.

CHECKDB found 0 allocation errors and 1 consistency errors in table 'tbl_NewOrder' (object ID 1342015912).

CHECKDB found 0 allocation errors and 4 consistency errors in database 'Power'.

Kristen
Test

22859 Posts

Posted - 2010-01-14 : 12:04:46

Run DBCC ('YourDatabaseName') UPDATEUSAGE - it will fix the "counts" on the tables.

Then I recommend re-running DBCC CHECKDB to make sure there are no other issues.

Maintaining these counts are supposed to be fixed in SQL 2005 (and supposedly "definitely fixed" in SQL 2008) - unless anyone disagrees with me??, so I'm a bit concerned that they are wrong on your database.

Was the database restored / migrated from SQL 2000? and DBCC UPDATEUSAGE not run at the time perhaps? That might be the cause.

DBA007
Posting Yak Master

145 Posts

Posted - 2010-01-14 : 12:35:25

Thanks Kristen,tht worked fine and i didnt received any pagecount error after running DBCC UPDATE Usage and DBCC CHECKDB.

I have got another problem in same Mplan,we have created database integrity,reorganizeindex,rebuildindex,update statistics,Maintenance cleanup task in the same Maintenanceplan.the plan was not working,I got the error messages as

Maintenance CleanUp Task:
Executing the query "EXECUTE master.dbo.xp_delete_file 1,N'',N'txt',N'2009-12-13T05:42:55'
" failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Update Statistics:
Executing the query "UPDATE STATISTICS [dbo].[tblsearch] WITH FULLSCAN" failed with the following error: "Possible index corruption detected. Run DBCC CHECKDB.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

ReorganizeIndex:
Executing the query "ALTER INDEX [PK_tblpos] ON [dbo].[tblpos] REORGANIZE WITH ( LOB_COMPACTION = ON )
" failed with the following error: "A severe error occurred on the current command. The results, if any, should be discarded.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Kristen
Test

22859 Posts

Posted - 2010-01-14 : 13:03:21

No idea on the errors you are seeing, although they do make me think that MS could put some more effort into pinpointing exactly what the issue is when such things get trapped.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-01-14 : 13:27:09

Two points,

Running both reorganizeindex and rebuildindex is a waste of time. Do some reading, figure out which you need and just run that. Or use Tara's (tkizer) index rebuild script.

That, again, looks like corruption. Same database as the one you ran update usage on? I strongly suggest running CheckDB (see previous code) on all databases on that instance, and doing some diagnostics of the IO subsystem.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources