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

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

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 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.

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

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.