Controlling Jobs

Question

Post reply

Controlling Jobs

Joe Doherty-260822

SSC Eights!

Points: 915
More actions
August 25, 2006 at 4:26 pm

#172210

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

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

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

Guus Kramer Ten Centuries Points: 1031 More actions · Answer 1

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 Kramer

The Netherlands

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 2

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

Richard Josey Ten Centuries Points: 1076 More actions · Answer 3

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.

Ravi Prashanth Lobo-275382 Right there with Babe Points: 792 More actions · Answer 4

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.

Herb Overstreet-363572 SSC Eights! Points: 886 More actions · Answer 5

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.

Charles Kincaid SSChampion Points: 13593 More actions · Answer 6

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

Joe Doherty-260822 SSC Eights! Points: 915 More actions · Answer 7

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

Herb Overstreet-363572 SSC Eights! Points: 886 More actions · Answer 8

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.

Charles Kincaid SSChampion Points: 13593 More actions · Answer 9

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

Richard Josey Ten Centuries Points: 1076 More actions · Answer 10

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.

Charles Kincaid SSChampion Points: 13593 More actions · Answer 11

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

Joe Doherty-260822 SSC Eights! Points: 915 More actions · Answer 12

OK guys - thanks for your input on that subject.

Anyone have anything else to discuss on the article?