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 Maintenance Job Running Long Expand / Collapse
Author
Message
Posted Tuesday, September 08, 2009 12:07 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 4:53 PM
Points: 94, Visits: 448
I created a rebuild index job to run weekly on Sundays at 3am. I just used the wizard to create the maintenance plan, nothing fancy. The job has been running fine for weeks, takes about 20 minutes each time. But this past sunday, it ran for over 24 hours, someone went in and canceled it because the job was running too long. Does anyone know why this would happen all of a sudden? It's been pretty consistent since we first created the job back in May so it's been running smoothly for the past 4 months. We are running SQL Server 2008 Standard (x64) and it has SP1.

Any ideas why the rebuild index job would suddenly run so long? Thanks for the help!
Post #784466
Posted Tuesday, September 08, 2009 1:45 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 5,849, Visits: 12,585
online or offline rebuild.? A sudden long run time is often due to the process being blocked out by another process.

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

Post #784546
Posted Tuesday, September 08, 2009 1:46 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 03, 2010 3:50 AM
Points: 146, Visits: 178
user activity? maybe some unfinished transaction ?
Post #784547
Posted Tuesday, September 08, 2009 3:23 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 4:53 PM
Points: 94, Visits: 448
It is an offline rebuild. I wonder if there was something that might cause it to be blocked but I wasn't the one that stopped the job at the time so I was able to check it out while it was still running. By the time i was notified of it, there wasn't any open transactions or any locks.
The job runs at 3am and is usually done by 3:20am. I'm not sure what caused it to run that long. Is there any other way I can figure out why the job took so long to run? Or is it pretty much nothing I can do at this point to find the cause?
Post #784603
Posted Tuesday, September 08, 2009 3:26 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 03, 2014 8:56 AM
Points: 120, Visits: 335
how big is the database. make sure you have enough disk space for it. almost need double the size of index sapce. also check in active proecesses if reindex is running.



Umar Iqbal
Post #784604
Posted Wednesday, September 09, 2009 7:50 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 5,849, Visits: 12,585
not much you can do post the event. set up some monitoring for the next run. Any new batch jobs been introduced that run at the same time?

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

Post #784962
Posted Tuesday, February 12, 2013 10:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 10:40 PM
Points: 6, Visits: 117
Did you change the Recovery Model? In FULL recovery model Index Rebuild takes long time due to logging of each and every page allocation.
Post #1419297
Posted Wednesday, February 13, 2013 3:31 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 5,849, Visits: 12,585
note: 3 year old thread!

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

Post #1419368
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse