SQLServerCentral / Discuss Content Posted by Joey Doherty / Article Discussions / Article Discussions by Author / Controlling Jobs / Latest Posts

RE: Controlling Jobs

Joe Doherty-260822 — Thu, 07 Sep 2006 10:47:00 GMT

OK guys - thanks for your input on that subject.

Anyone have anything else to discuss on the article?

RE: Controlling Jobs

Charles Kincaid — Thu, 07 Sep 2006 10:09:00 GMT

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.

RE: Controlling Jobs

Richard Josey — Thu, 07 Sep 2006 03:19:00 GMT

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.

RE: Controlling Jobs

Charles Kincaid — Wed, 06 Sep 2006 12:01:00 GMT

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.

RE: Controlling Jobs

Herb Overstreet-363572 — Wed, 06 Sep 2006 11:43:00 GMT

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.

RE: Controlling Jobs

Joe Doherty-260822 — Wed, 06 Sep 2006 11:06:00 GMT

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.

Joe

RE: Controlling Jobs

Charles Kincaid — Wed, 06 Sep 2006 10:49:00 GMT

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.

RE: Controlling Jobs

Herb Overstreet-363572 — Wed, 06 Sep 2006 10:36:00 GMT

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.

RE: Controlling Jobs

Ravi Prashanth Lobo-275382 — Wed, 06 Sep 2006 08:53:00 GMT

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.

RE: Controlling Jobs

Richard Josey — Wed, 06 Sep 2006 08:52:00 GMT

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.

RE: Controlling Jobs

Sreejith Sreedharan — Wed, 06 Sep 2006 07:48:00 GMT

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?

Thanks

Sreejith

RE: Controlling Jobs

Guus Kramer — Wed, 06 Sep 2006 00:05:00 GMT

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.

Regards,Guus KramerThe Netherlands

Controlling Jobs

Joe Doherty-260822 — Fri, 25 Aug 2006 16:26:00 GMT

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