?
Solved

Error when trying to Re-Index Database

Posted on 2012-09-15
4
Medium Priority
?
1,436 Views
Last Modified: 2012-09-21
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.
0
Comment
Question by:Richard Comito
  • 2
  • 2
4 Comments
 
LVL 16

Accepted Solution

by:
DcpKing earned 1500 total points
ID: 38402590
"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
 

Author Comment

by:Richard Comito
ID: 38403677
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
 

Author Closing Comment

by:Richard Comito
ID: 38422701
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
 
LVL 16

Expert Comment

by:DcpKing
ID: 38424043
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question