SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rebuild Index Maintenance Job Running Long


Rebuild Index Maintenance Job Running Long

Author
Message
KT8177
KT8177
SSC-Addicted
SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)

Group: General Forum Members
Points: 458 Visits: 494
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!
george sibbald
george sibbald
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59054 Visits: 13706
online or offline rebuild.? A sudden long run time is often due to the process being blocked out by another process.

---------------------------------------------------------------------
Marcin Gol
Marcin Gol
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2066 Visits: 178
user activity? maybe some unfinished transaction ?
KT8177
KT8177
SSC-Addicted
SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)

Group: General Forum Members
Points: 458 Visits: 494
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?
umar iqbal
umar iqbal
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1030 Visits: 395
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 Alien

Umar Iqbal
george sibbald
george sibbald
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59054 Visits: 13706
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?

---------------------------------------------------------------------
Rajeev Ramachandran
Rajeev Ramachandran
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 126
Did you change the Recovery Model? In FULL recovery model Index Rebuild takes long time due to logging of each and every page allocation.
george sibbald
george sibbald
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59054 Visits: 13706
note: 3 year old thread!

---------------------------------------------------------------------
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search