Rebuild Index Maintenance Job Running Long

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Rebuild Index Maintenance Job Running Long

Rebuild Index Maintenance Job Running Long

Rate Topic

Display Mode

Topic Options

Author

Message

KT8177

Posted Tuesday, September 08, 2009 12:07 PM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 4:46 PM
Points: 94, Visits: 392

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

george sibbald

Posted Tuesday, September 08, 2009 1:45 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 5,317, Visits: 11,307

online or offline rebuild.? A sudden long run time is often due to the process being blocked out by another process.

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

Post #784546

Marcin Gol

Posted Tuesday, September 08, 2009 1:46 PM

SSC-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

KT8177

Posted Tuesday, September 08, 2009 3:23 PM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 4:46 PM
Points: 94, Visits: 392

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

umar iqbal

Posted Tuesday, September 08, 2009 3:26 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 118, Visits: 281

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.

Crazy

Umar Iqbal

Post #784604

george sibbald

Posted Wednesday, September 09, 2009 7:50 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 5,317, Visits: 11,307

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

Rajeev Ramachandran

Posted Tuesday, February 12, 2013 10:44 PM

Forum 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

george sibbald

Posted Wednesday, February 13, 2013 3:31 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 5,317, Visits: 11,307

note: 3 year old thread!

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

Post #1419368

« Prev Topic | Next Topic »

Permissions