Hi Community Experts of SQLServerCentral, We have a 20 databases, We are used to use maintenance Plan to schedule the Weekly rebuild index job on Every Saturday night 12pm IST. Last Two Weeks Weekly Rebuild Index job Running long over 2days? But acutally it might run 30 mints. Which is caused this? How to fix to running shortly? I looked at some SQL access logs, and I didn't see anything unusual. I attached screen shots for your ref: ![alt text] : /storage/temp/761-weeekly+index+rebuild+job+runs+very+long.jpg
Does the job breakdown which of the 20 databases the index rebuild took the time for. Di the transaction log grow considerably for one of the database to justify that amount of time. If none of these it suggest a lock contention which held everything up so you could look at sp_who2 to see if there is a blocking process or delve deeper into locks to find out which object has lock escalation. What type of replication are you running?
If job run for 2 days for last one or two times then check as below. what all processes/Queries are running during rebuilding process. Check for process that obstructing the rebuilding. Check the index size that might be increased which caused increase in time for rebuilding. And, i hope database not configured for mirroring which can cause the rebuilding process time increase.
I would monitor the index fragmentation using this article
http://msdn.microsoft.com/en-us/library/ms188917.aspx. If the table is largely read only and there has been no significant changes in the number of rows then I don't believe it is necessary to re-index every week as it will make no difference to performance.