Error when trying to Re-Index Database

I have a job that runs 3 times a week to re-index my database.  the last few times I have gotten the following error and I am not sure what course I should take to fix this error.  Any advice would be helpful.

Thanks,


-----------------------------------------------------------------------------------------------------------------------------------------------


Date            9/14/2012 1:30:00 AM
Log            Job History (Rebuild Index.Subplan_1)

Step ID            1
Server            VM-TPROD\TESSI
Job Name            Rebuild Index.Subplan_1
Step Name            Subplan_1
Duration            00:55:48
Sql Severity            0
Sql Message ID            0
Operator Emailed            
Operator Net sent            
Operator Paged            
Retries Attempted            0

Message
Executed as user: LA_OPERA\tessmanagerlive. ...2500.0 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  1:30:00 AM  Progress: 2012-09-14 01:30:02.34     Source: {B8A59DC3-F06B-4724-9067-18C1BB14D58F}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  Progress: 2012-09-14 01:30:20.94     Source: Rebuild Index      Executing query "USE [impresario]  ".: 0% complete  End Progress  Progress: 2012-09-14 01:30:21.07     Source: Rebuild Index      Executing query "ALTER INDEX [ind1] ON [dbo].[C_ADV_CUST] REBUILD P...".: 0% complete  End Progress  Progress: 2012-09-14 01:30:21.07     Source: Rebuild Index      Executing query "USE [impresario]  ".: 0% complete  End Progress  Progress: 2012-09-14 01:30:21.08     Source: Rebuild Index      Executing query "ALTER INDEX [ind2] ON [dbo].[C_ADV_CUST] REBUILD P...".: 0% complete  End Progress  Progress: 2012-09-14 01:30:21.08     Source: Rebuild Index      Executing query "USE [impresario]  ".: 0% complete  End Progress  Progress: 2012-09-14 01:30:21.08     Source: Rebuild Index      Executing query "ALTER INDEX [PK_C_ADV_CUST_1__14] ON [dbo].[C_ADV_...".: 0% complete  End Progress  Progress: 2012-09-14 01:30:21.08     Source: Rebuild Index      Executing query "USE [impresario]  ".: 0% complete  End Progress  Progress: 2012-09-14 01:30:21.08     Source: Rebuild Index      Executing query "ALTER INDEX [PK_C_ADV_DEL_ADDRESS_1__14] ON [dbo]....".: 0% complete  End Progress  Progress: 2012-09-14 01:30:21.08     Source: Rebuild Index      Executing query "USE [impresario]  ".: 0% complete  End Progress  Progress: 2012-09-14 01:30:21.13     Source: Rebuild Index      Executing query "ALTER INDEX [PK_C_CONVERSION_LOG] ON [dbo].[C_CONV...".: 0% complete  End Progress  Progress: 2012-09-14 01:30:21.13     Source: Rebuild Index      Executing query "USE [impresario]  ".: 0% complete  End Progress  Progress: 2012-09-14 01:30:21.16     Source: Rebuild Index      Executing query "ALTER INDEX [sss] ON [dbo].[C_GS_PURCHASE] REBUILD...".: 0% complete  End Progress  Progress: 2012-09-14 01:30:21.16     Source: Rebuild Index      Executing query "USE [impresario]  ".: 0% complete  End Progress  Progress: 2012-09-14 01:30:21.16     Source: Rebuild Index      Executing query "ALTER INDEX [PK___1__21] ON [dbo].[c_nc] REBUILD P...".: 0% complete  End Progress  Progress: 2012-09-14 01:30:21.16     Source: Rebuild Index      Executing query "USE [impresario]  ".: 0% complete  End Progress  Progress: 2012-09-14 01:30:21.16     Source: Rebuild Index      Executing query "ALTER INDEX [sss] ON [dbo].[c_nc] REBUILD PARTITIO...".: 0% complete  End Progress  Progress: 2012-09-14 01:30:21.16     Source: Rebuild Index      Executing query "USE [impresario]  ".: 0% complete  End Progress  Progress: 2012-09-14 01:30:21.16     Source: Rebuild Index      Executing query "ALTER INDEX [PK___2__32] ON [dbo].[c_nopromote_pho...".: 0% complete  End Progress  Progress: 2012-09-14 01:30:21.16     Source: Rebuild Index      Executing query "USE [impresario]  ".: 1% complete  End Progress  Progress: 2012-09-14 01:30:21.23     Source: Rebuild Index      Executing query "ALTER INDEX [PK_C_PLANNED_GIVING_1__12] ON [dbo].[...".: 1% complete  End Progress  Progress: 2012-09-14 01:30:21.23     Source: Rebuild Index      Executing query "USE [impresario]  ".: 1% complete  End Progress  Progress: 2012-09-14 01:30:21.30     Source: Rebuild Index      Executing query "ALTER INDEX [PK_C_PROMOTION2_1__20] ON [dbo].[C_PR...".: 1% complete  End Progress  Progress: 2012-09-14 01:30:21.30     Source: Rebuild Index      Executing query "USE [impresario]  ".: 1% complete  End Progress  Progress: 2012-09-14 01:30:21.36     Source: Rebuild Index      Executing query "ALTER INDEX [sss] ON [dbo].[ccc_li] REBUILD PARTIT...".: 1% complete  End Progress  Progress: 2012-09-14 01:30:21.36     Source: Rebuild Index  ...  The package execution fa...  The step failed.
Richard ComitoDirector of ITAsked:
Who is Participating?
 
DcpKingCommented:
"Executing query "ALTER INDEX [sss] ON [dbo].[ccc_li] REBUILD PARTIT...".: 1% complete  End Progress  Progress: 2012-09-14 01:30:21.36     Source: Rebuild Index  ...  The package execution fa...  The step failed. "

Are the ellipsis your insertion or did the message really fail in the middle of a word?

I'd suggest you use DBCC to check the database and see if you have some corruption that's showing itself here.

http://msdn.microsoft.com/en-us/library/ms176064.aspx

hth

Mike
0
 
Richard ComitoDirector of ITAuthor Commented:
thanks Mike for the reply.  I had just copied and pasted the error so I would say that the message failed in the middle of a word.

I ran a check db and this is what I got below.  it looks like I am going to have to do a repair but I am not sure which one.  I am hoping that I can just run the CHECK DB with REPAIR_REBUILD.  this is on a production server and the error has been happening for awhile so I don't think that a restore of a backup would help.

Is there any suggestions on how I should run the CHECK DB?

Thanks,

Rich





Message
Executed as user: . Microsoft (R) SQL Server Execute Package Utility  Version 10.50.2500.0 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  4:00:01 AM  Progress: 2012-09-15 04:00:11.86     Source: {6D4133E6-BCB5-4337-A9EB-56B83774DFAC}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  Progress: 2012-09-15 04:00:13.77     Source: Check Database Integrity      Executing query "USE [impresario]  ".: 50% complete  End Progress  Progress: 2012-09-15 04:30:29.24     Source: Check Database Integrity      Executing query "DBCC CHECKDB(N'impresario')  WITH NO_INFOMSGS  ".: 100% complete  End Progress  Progress: 2012-09-15 04:30:29.24     Source: Check Database Integrity      Executing query "USE [impresario_cci]  ".: 50% complete  End Progress  Error: 2012-09-15 04:32:39.89     Code: 0xC002F210     Source: Check Database Integrity Execute SQL Task     Description: Executing the query "DBCC CHECKDB(N'impresario_cci')  WITH NO_INFOMSGS ..." failed with the following error: "Table error: Object ID 885578193, index ID 1, partition ID 339512229167104, alloc unit ID 339512229167104 (type In-row data). Page (1:467286) is missing a reference from previous page (1:467502). Possible chain linkage problem.  Object ID 885578193, index ID 1, partition ID 339512229167104, alloc unit ID 339512229167104 (type In-row data): Page (1:467502) could not be processed.  See other errors for details.  Table error: Object ID 885578193, index ID 1, partition ID 339512229167104, alloc unit ID 339512229167104 (type In-row data), page (1:467502), row 0. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 3840 and 4344.  Table error: Object ID 885578193, index ID 1, partition ID 339512229167104, alloc unit ID 339512229167104 (type In-row data), page (1:467502), row 0. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 3840 and 4344.  Table error: Object ID 885578193, index ID 1, partition ID 339512229167104, alloc unit ID 339512229167104 (type In-row data). Page (1:467502) was not seen in the scan although its parent (1:486260) and previous (1:486003) refer to it. Check any previous errors.  CHECKDB found 0 allocation errors and 5 consistency errors in table 'T_CUST_KEYCODE' (object ID 885578193).  CHECKDB found 0 allocation errors and 5 consistency errors in database 'impresario_cci'.  repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (impresario_cci).". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  4:00:01 AM  Finished: 4:32:43 AM  Elapsed:  1962.07 seconds.  The package execution failed.  The step failed.
0
 
Richard ComitoDirector of ITAuthor Commented:
There was no follow up but I was able to get the info that I needed by using this thread as a good starting point.
0
 
DcpKingCommented:
Sorry Rich - life intervened :(

Use CHECKDB CHECKTABLE

(ref http://msdn.microsoft.com/en-us/library/ms174338.aspx)

use it first to explore what it can find out, and then to try to repair the table.

First, though, I'd try to copy all the data from the table (select * into backuptable from table). In fact, if you get all the data back, and you can build all the indices, etc. that you need on the new table, then you might just kill off the old one .....

Mike
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.