question

askmlx121 avatar image
askmlx121 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
sql-server-2005indexmaintenancerebuild
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tanglesoft avatar image
tanglesoft answered
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?
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

askmlx121 avatar image askmlx121 commented ·
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???
0 Likes 0 ·
NeerajTripathi avatar image
NeerajTripathi answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

askmlx121 avatar image askmlx121 commented ·
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????
0 Likes 0 ·
tanglesoft avatar image
tanglesoft answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.