Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Controlling Jobs Expand / Collapse
Author
Message
Posted Friday, August 25, 2006 4:26 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 25, 2014 2:12 PM
Points: 64, Visits: 131
Comments posted to this topic are about the content posted at temp
Post #304277
Posted Wednesday, September 6, 2006 12:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 11, 2009 7:15 AM
Points: 129, 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

Post #306474
Posted Wednesday, September 6, 2006 7:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:42 PM
Points: 394, Visits: 84

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

Post #306520
Posted Wednesday, September 6, 2006 8:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 5, 2013 5:07 PM
Points: 310, 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.
Post #306548
Posted Wednesday, September 6, 2006 8:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 20, 2007 12:30 PM
Points: 24, 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.

Post #306549
Posted Wednesday, September 6, 2006 10:36 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 7, 2010 6:52 PM
Points: 516, 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.
Post #306597
Posted Wednesday, September 6, 2006 10:49 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962

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.

 



ATB

Charles Kincaid

Post #306604
Posted Wednesday, September 6, 2006 11:06 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 25, 2014 2:12 PM
Points: 64, Visits: 131

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

Post #306610
Posted Wednesday, September 6, 2006 11:43 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 7, 2010 6:52 PM
Points: 516, 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.
Post #306622
Posted Wednesday, September 6, 2006 12:01 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962

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.

 



ATB

Charles Kincaid

Post #306633
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse