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-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10566 Visits: 13687
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
Old Hand
Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)Old Hand (334 reputation)

Group: General Forum Members
Points: 334 Visits: 178
user activity? maybe some unfinished transaction ?
KT8177
KT8177
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 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
SSC Veteran
SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)SSC Veteran (230 reputation)

Group: General Forum Members
Points: 230 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10566 Visits: 13687
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
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10566 Visits: 13687
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