February 26, 2006 at 8:11 pm
Hi all, hope someone has some ideas as I am a little stuck.
Scenario is sql7 DTS package that performs FTP amongst other things. This DTS package is run via scheduled job. Issue is that FTP occasionally hangs and will go unnoticed. The job runs several times a day but the job won't run again if it failed to finish on previous run, untill forcibly stopped.
So any ideas of how to monitor the ftp process and say after 1 hour kill it. In this case the next run will perform the work as the package pretty much truncates data and refreshes it. Or is there a method to use an agent driven alert to say email someone(again an alert for a job that has not yet finished, is this possible).
Any ideas or solutions would be appreciated.
Derek
February 28, 2006 at 7:00 am
Unfortunately, it not simple. Check out this link: http://www.databasejournal.com/features/mssql/article.php/3500276
The link above uses the undocumented extended stored procedure xp_sqlagent_enum_jobs, so it's probably not recommended for a production system.
Another (brute force) way would be to schedule another job for 1 hour following each of the execution times of the job you are tracking. The new job would run msdb..sp_stop_job. In the Advanced tab of the New Job Step window, set the On failure Action to "Quit the job reporting success".
March 1, 2006 at 6:14 pm
Thanks very much for the ideas. Didn't know of sp_stop_job. I love the brute force approach and even though this is prod it will do just fine. Agree this is better than unsupported sp's. Thanks again will give this a go.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply