asked
Weekly Rebuild Index job Running long over 2days?
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][1] [1]: /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?
we have running Snapshot Replication in our Server. As you are right 1 out of 20 databases are problem in deadlock at every weekend (i.e) someone left an SSMS window open with an uncomitted transaction that blocked the index rebuild. Monday morning it closed the window and allowed the task to complete that causes Weekly Index Rebuild Job Runs long I found one table one index rebuild took long time why????? how to solve this? Is it Necessary to run Weekly rebuild index job on every week???
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.
Hi Maverick, There is no Processes/Queries are running that time. We are using Replication job runs before this job running But Last two weeks there is no job fails in replication But after long analyse I found one database of one table had deadlock This weekend I experienced some odd behavior from one table. I could not run a simple select query on this table So it might relate to index rebuild fail isnt????
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.
15 People are following this question.
