SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Rebuild Index
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DBA007
Posting Yak Master

India
145 Posts

Posted - 01/14/2010 :  11:04:18  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/14/2010 :  11:22:36  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

DBA007
Posting Yak Master

India
145 Posts

Posted - 01/14/2010 :  11:36:57  Show Profile  Reply with Quote
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'.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/14/2010 :  12:04:46  Show Profile  Reply with Quote
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.

Edited by - Kristen on 01/14/2010 12:06:02
Go to Top of Page

DBA007
Posting Yak Master

India
145 Posts

Posted - 01/14/2010 :  12:35:25  Show Profile  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/14/2010 :  13:03:21  Show Profile  Reply with Quote
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.

Edited by - Kristen on 01/14/2010 13:05:58
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/14/2010 :  13:27:09  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000