SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Controlling Jobs


Controlling Jobs

Author
Message
Joe Doherty-260822
Joe Doherty-260822
Old Hand
Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)

Group: General Forum Members
Points: 387 Visits: 180
Comments posted to this topic are about the content posted at temp
Guus Kramer
Guus Kramer
SSC-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

Group: General Forum Members
Points: 427 Visits: 12

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
Sreejith Sreedharan
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2305 Visits: 89

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
Richard Josey
Mr or Mrs. 500
Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)

Group: General Forum Members
Points: 556 Visits: 26
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
Ravi Prashanth Lobo-275382
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 Visits: 1

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
Herb Overstreet-363572
SSChasing Mays
SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)

Group: General Forum Members
Points: 656 Visits: 46
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
Charles Kincaid
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4885 Visits: 2384

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
Joe Doherty-260822
Old Hand
Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)Old Hand (387 reputation)

Group: General Forum Members
Points: 387 Visits: 180

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
Herb Overstreet-363572
SSChasing Mays
SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)SSChasing Mays (656 reputation)

Group: General Forum Members
Points: 656 Visits: 46
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
Charles Kincaid
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4885 Visits: 2384

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search