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 |
|
cornall
Posting Yak Master
148 Posts |
 Posted - 2007-05-10 : 10:40:17
|
| I have a stored procedure that restores log files. I make it recursivly call itself. WAITFOR DELAY '00:05:00';exec DBA_RestoreFullBackupsAndLogs_spI don't want to make it a job as it can take more than 5 min to run so if I tell it to run every 5 mins It might fall over itself.Is there any way to check it it still running ok? Other than making it write to a table each recursion and checking that table with a job to see if it went ok?Or am I being daft would the job wait till it had finished before running again.Hope this makes sense!D |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-05-10 : 12:13:56
|
| It would make much more sense to have a job for this. You can track it back via Job History, get notifications if it failed...Dinakar NethiSQL Server MVP************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-10 : 12:20:03
|
| >> I don't want to make it a job as it can take more than 5 min to run so if I tell it to run every 5 mins It might fall over itself.If you schedule it every 5 mins and it takes linger than 5 mins it will just miss a schedule, it won't try to run it twice.When it completes it will be rescheduled for the next run.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-10 : 14:04:02
|
| "Or am I being daft would the job wait till it had finished before running again"Yes (as Nigel has said, but just to clarify!)Kristen |
|
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2007-05-11 : 03:33:09
|
| Excelent thats good. Glad I was just being daft. I was just worried the job would kick off multiple copies of the sp all trying to process the same files! |
|
|
|
james_b
Starting Member
9 Posts |
Posted - 2007-05-11 : 05:29:56
|
| the "sp_help_job" proc may be of use to you as it returns a list of all jobs with a given status. E.g to show a list of all currently executing jobs, try:exec msdb.dbo.sp_help_job @execution_status = 1Have a look at the following article:http://www.databasejournal.com/features/mssql/article.php/10894_3491201_2hope this helps :)james |
|
|
|
|
|
|
Can I get a list of running stored procedures