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 Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 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
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25414 Visits: 13701
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
SSC Eights!
SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)

Group: General Forum Members
Points: 948 Visits: 178
user activity? maybe some unfinished transaction ?
KT8177
KT8177
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 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-Addicted
SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)

Group: General Forum Members
Points: 468 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
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25414 Visits: 13701
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 (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25414 Visits: 13701
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