Controlling Jobs

  • Comments posted to this topic are about the content posted at temp

  • Joey,

    We face the same problem on Indexes. However I just need one simple job.

    Create and SP on one of you databases on your server and executed it using a job

    The content of this SP ==>

    exec sp_msforeachtable @command1="print'?' dbcc dbreindex ('?')"

    Yes....thats all

    Running this on a full operational 100 Gb OLTP database takes just 2 hours to compete.


    Guus Kramer

    The Netherlands

  • Joe,

    Have you thought about what will happen if the "REINDEX" within the loop takes 2 hours? SQL will issue the REINDEX and wait for it to be completed so your GETDATE() < @FinishTime will be ingnored within the loop. Any thoughts / ideas?



  • Another way to do this is to schedule another job to schedule an sp_stop_job. This stops the job in a far less graceful way but at the time you specify rather than the period to complete the loop.

    Your solution will always overrun by an unknown amount.

    One way to do a one-off reindex would be to create a table with all the table names/ids in and a reindexed 0,1 bit column which got updated after the reindex statement.

    This way the job would not start at the beginning again.

    For ongoing maintenance you could use a date column and select your tables on reverse date order.

    These would work with both the loop and hard kill techniques.

  • This seems to be interesting.


    I think REINDEX has been used in the article just as an example. But the concept can be used anywhere.


    Thank you, it is a good point.

  • Great article that can be used for almost any operation. But as you can see you will always get suggestions as to "How to something", but why all these didn’t "this is better" or "you can get the same by" contributors put their fingers to a keyboard and do it before now. Thanks for taking the time to submit your method.

  • Herb one reason might be employement contracts.  One might be allowed to participate in a forum but if that same person publishes an article the employer might actually own it and the rights thereto.


    ATBCharles Kincaid

  • Sreejith,

    I did mention this point in the article. I am simply trying to illustrate the point of using the GETDATE() function and a cursor as one way of controlling when a job might might end. There are limitations.

    The article was was aimed at the beginner as well as suggesting an idea. Perhaps the reindex was a bad example to use but I hoped this would start the reader off so that they could continue and experiment with something that would work for themself.


  • Charles: Thanks for the enlightment (not being smart). I have never considered that and I am not one to be PC. I think in the future I'll do my responses from home.

  • I participate in forums as a way to demostrate that I am keeping up on industry standards and best practices.  I am very careful not to post trade secrets or customer information.  Good employers recognize this.  Some places even have a time code for this under continuing education.

    I do this while I have long running jobs going, on lunch, or after hours.


    ATBCharles Kincaid

  • Joe,

    Thanks for the article and the technique. I like the idea and think it will work well in a number of situations.

    I am unsure why 'Herb' is attacking the subsequent contributors though. Surely discussing the pro's and con's of various ways of tackling the issue adds to the value of the article.

    It also may provide feedback on con's the author had not thought of.

    I personally like the technique above but it does not ensure the last task completes which is sometimes required.

  • An open discussion can bring out many good things.  Herb is OK though.  It is right to question everything.  Even the forum process.  Questions are the things that spur research.

    We all have ideas.  Some good, some not so good.  It is by putting forth the ideas that we see if they can stand and walk on their own (like children).  We can openly debate ideas and techniques.  We must not debate people.  If some one is out of line, that should be handled one-on-one and off line.  I don't see anyone out of line here.

    Outside phycists and cosmologists we IT folk have just about the most free and open discussions of any group that I have been part of.

    ATBCharles Kincaid

  • OK guys - thanks for your input on that subject.

    Anyone have anything else to discuss on the article?

Viewing 13 posts - 1 through 12 (of 12 total)

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