Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rwaldron
Posting Yak Master
131 Posts |
 Posted - 2008-06-06 : 06:31:05
|
| Hi all.I have about 20 stored procedures that run SELECT INTO queries..I previously had these running as individual jobs scheduled to run every say 10 mins but this started to cause performance issues.Now I have Grouped these stored procedures as seperate steps in 3 Main Jobs.Effectively now I have the SP's running in serial.My question now is in regard to scheduling these jobs.If say Job1 has 10 steps,each a stored procedure takes 10Mins total to run.The next time this may take 30Mins to run.I want to shcedule this to run but how often do I schedule it?ie:If I schedule it to run every 20Mins and the total running time takes 30Mins will SQL wait and only run it again 20mins AFTER it has completed...So My main qiestion is in SQL does the schedule to repeat every 20Mins mean 20 Mins after the Job has completed(However long it takes)the job will start again..I ask this as I don't want to create an exponentially growing job.Thanks,Ray.. |
|
|
rgombina
Constraint Violating Yak Guru
319 Posts |
Posted - 2008-06-06 : 08:31:15
|
| How about executing SPs within SQL Task and use precedence to sequence the processes? SQL job of 20 minutes interval means just that regardless of what your package is doing.Also, you may want to check on Sequence Container to group the processes. You can also add System.Threading.Thread.Sleep(time) to pause between tasks using Script Task. |
 |
|
|
srw16505
Starting Member
2 Posts |
Posted - 2008-06-06 : 09:28:06
|
| The schedule will allow the job to repeat every 20 minutes from the start time of the schedule. By default that would be 12:00:00 am, which means the job is scheduled to execute at :00, :20, and :40 after the hour. If the job is still running its previous run when its next start time hits, the original job keeps running and this current run will just be skipped. It will then execute again when the next start time occurs. You should also likely see an entry in your Agent Error Log regarding Agent not being able to start the job because it is already running. |
|
|
|
|
|
|
Scheduled Job Optimisation.