How to set the next job run time randomly in SQL Server 2000

Question

Here is the scenario:

We have a database in SQL Server 2000. The application that connects to this database randomly times out with different errors. We have run traces, but were not successful in catching the time out issue. Strangely, whenever we run the trace, the app does not time out.

The plan is to setup a job to run traces. Considering this is a production box, we do not want to run traces for too long too frequently. So the idea is to run a trace for 3 mintues.

Here is the challenge - We want to randomize the next run time - between 10 and 60 minutes. I have tried updating the next_run_time in msdb..sysjobschedules, but that does not seem to have any effect.

Any ideas?

Thanks in advance,

Raj

Answer 1

Take a look at the sp_update_jobschedule procedure.