Rebuild index job runs for a long time

Question

Rebuild index job runs for a long time

Chitown

Hall of Fame

Points: 3239
More actions
December 7, 2015 at 10:23 am

#399436

I have Index Rebuild job which usually runs everyweek and takes about 15-20 minutes (Average time). Couple of times, it ran for 2+ hours. I found this script online http://devinknightsql.com/2012/07/07/identifying-long-running-sql-agent-jobs-script/ and I was hoping that it 'd work but it didn't. I created a small job which was running every minute. Select * from sometable. Then I edited the job WAITFOR delay '000:05:00'
Select * from sometable
Then executed the SP but it returned 0 value.
So this is where I need some help.
I 'd like to be notified if the job runs for a long time. How can I accomplish that?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

er.mayankshukla SSCarpal Tunnel Points: 4190 More actions · Answer 1

Are you scheduling this operation with Sql Agent Job, if yes then there may not be a direct solution for you however you can use some 3rd party Sql Monitors like SqlSentry (http://www.sqlsentry.com/)

However, I would suggest a workaround for this.

In the procedure or the query where you are rebuilding index, create some table with a flag column like (Rebuild Status) and a starttime column

At the start of rebuilding operation, update the flag value as 'Started' and update the start time,

and post completion of rebuilding update the status as 'Completed'

Create a second job, where you keep polling this flag value with started and start time, compare with current time. If that exceeds the time you desire like (15-20 mins) raise a custom error message.

This part you can do with a small T-Sql code.

Then you can even configure an email associated with that custom error.

I guess this work around will save you from buying some paid solutions or if someone has a better solution most welcome 🙂

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Chitown (12/7/2015)
I have Index Rebuild job which usually runs everyweek and takes about 15-20 minutes (Average time). Couple of times, it ran for 2+ hours. I found this script online http://devinknightsql.com/2012/07/07/identifying-long-running-sql-agent-jobs-script/ and I was hoping that it 'd work but it didn't. I created a small job which was running every minute. Select * from sometable. Then I edited the job WAITFOR delay '000:05:00'
Select * from sometable
Then executed the SP but it returned 0 value.
So this is where I need some help.
I 'd like to be notified if the job runs for a long time. How can I accomplish that?

Ok... let's just say that you get a script working to do such a notification for your index rebuild job and it sends you an email that the job is or has taken longer than expected....

... just exactly what are you going to do about it when you get the email?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Chitown Hall of Fame Points: 3239 More actions · Answer 3

Jeff Moden (12/7/2015)Ok... let's just say that you get a script working to do such a notification for your index rebuild job and it sends you an email that the job is or has taken longer than expected....
... just exactly what are you going to do about it when you get the email?

I will try to find out why it is taking that long. Maybe re-run the job some other time. Maybe there is a process being blocked out by another process, etc

Michael L John One Orange Chip Points: 27055 More actions · Answer 4

What are you using for reindexing? A maintenance plan?

There are a number of free maintenance solutions that log what they are doing.

They capture the indexes that are fragmented, and then only reindex the things that need it.

We use:

https://ola.hallengren.com/

That way, you can simply look in the logs, or, in the case of Ola's scripts, the logging table to see exactly what has happened and why.

The simple answer is that there are more things that needed to be re-indexed. What caused the indexes to become fragmented in the first place?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Alexander Suprun SSCertifiable Points: 6253 More actions · Answer 5

These 2 parameters can be particularly useful in your case:

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
TimeLimit
Set the time, in seconds, after which no commands are executed. By default, the time is not limited.
LockTimeout
Set the time, in seconds, that a command waits for a lock to be released. By default, the time is not limited.
The LockTimeout option in IndexOptimize uses the SET LOCK_TIMEOUT set statement in SQL Server.

Alex Suprun