July 19, 2010 at 1:08 pm
Hi,
We have some sqlserver 2000 dts jobs that runs all the times. These jobs require constant manual overseeing to check to see if they are taking longer run time and make sure all of them completes successfully. Sometimes these jobs gets stuck(taking more than 3-4 hours) in which case and had to manually stop them to make them re run again.
My question is whether this process of manually stopping of these jobs(those taking longer) can be done using a program(typically in vb.net/C#) which would save us quite a bit of time?
thanks in advance,
TZ
July 19, 2010 at 1:15 pm
Yes it can be done via VB.NET, anything that can be done manually can be done programmatically. You will need to write business logic into the program that determines when it is stuck, etc. You will need to query the msdb system tables to determine how long something has been running, etc. You've got a bit of legwork to do to get the VB.NET program going, but it's all available to be queried.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
July 19, 2010 at 2:48 pm
The stopping/restarting could pontentially be done in T-SQL as well (Sp_start_job, sp_stop_job).
However be sure that if you re-run packages that they can restart themselves without issue and not do the same work twice.
July 19, 2010 at 3:23 pm
Hi,
Thanks for your replies. Do you have a small TSQL script that shows me how to do that?
thanks again,
TZ
July 19, 2010 at 4:18 pm
do what? stop jobs? just call sp_start_job. You can read about the parameters in Books Online. Same for sp_start_job.
You can schedule this to run, and here's a short discussion of checking on current status
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply