Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rebuild Index Job fails after running for 10 hours and after completing 80%. Expand / Collapse
Author
Message
Posted Saturday, August 23, 2014 4:02 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:21 PM
Points: 417, Visits: 2,558
Hi Guys,

Here is the scenario that I am facing and not exactly sure how to go about it. Please let me know your thoughts.

I have a database with 1.5 TB in size. Rebuild Index Job fails after running for 10 hours and after completing 80%(last week, it failed due to deadlock). The same database is on a Replication Configuration on Publisher role. During the Rebuil Index run, I noticed Replication Log Reader is constantly blocked by Rebuild Index Job. Rebuild Index doesn't seems to make any progress, it's stuck at 33%.

Is there anything we can do to to Replication to complete Rebuild Index job successfully?


Regards,
SQLisAwe5oMe.
Post #1606792
Posted Saturday, August 23, 2014 4:27 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:49 AM
Points: 1,914, Visits: 5,275
Quick question, what edition of SQL Server are you on?
Post #1606796
Posted Saturday, August 23, 2014 7:10 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:21 PM
Points: 417, Visits: 2,558
It's 2008r2, enterprise edition on Windows 2008.

Regards,
SQLisAwe5oMe.
Post #1606820
Posted Sunday, August 24, 2014 12:44 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:49 AM
Points: 1,914, Visits: 5,275
SQLisAwE5OmE (8/23/2014)
It's 2008r2, enterprise edition on Windows 2008.


Are you using the ONLINE option? If you are, the additional resources required might be part of the problem. If not, in might also be worth giving it a shot.
Post #1606830
Posted Monday, August 25, 2014 12:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 3:36 AM
Points: 82, Visits: 119
SQLisAwE5OmE (8/23/2014)
Hi Guys,

Here is the scenario that I am facing and not exactly sure how to go about it. Please let me know your thoughts.

I have a database with 1.5 TB in size. Rebuild Index Job fails after running for 10 hours and after completing 80%(last week, it failed due to deadlock). The same database is on a Replication Configuration on Publisher role. During the Rebuil Index run, I noticed Replication Log Reader is constantly blocked by Rebuild Index Job. Rebuild Index doesn't seems to make any progress, it's stuck at 33%.

Is there anything we can do to to Replication to complete Rebuild Index job successfully?


Index should be reorganized when index fragmentation is between 10-44 %. Rebuilding process uses many more CPU and blocks the database resources. Please turn on online option.
Post #1606967
Posted Monday, August 25, 2014 6:12 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:49 AM
Points: 1,914, Visits: 5,275
Quick question, how beefy is the index/indexes being rebuilt? Would it be an option to build an identical index with the ONLINE option and then drop the existing one afterwards?
Post #1607028
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse