March 1, 2010 at 4:23 am
Hi;
not sure if this is the correct forum, if not can you suggest.
anyway. i have a sql job scheduled to run a ssis package that appears to just stop. The schedule is set to run a ssis package every 3 minutes between 8am and 7pm weekdays indefinitely. is should never stop.
i get an error if i try to start it, so i have top stop it first and then start it, but it is certainly not running as the last execution of the jobs is out with the 3minutes period. i have e-mail alerts set up for when the job fails, but i never receive any e-mails. i only discover this when users complain.
any ideas, i don’t even know where to start looking.
March 1, 2010 at 6:24 am
just a couple of basics; if the job starts but takes longer than 3 minutes to complete,the next scheduled job will not run, and the schedule will not resume until/ if the first job completes; could your job be caught in an infinite loop or is not exiting/ending for some reason?
if you show us the code/procedure you are running every 3 minutes, we might be able to point out a better way to do it, or an alternative to what it is doing altoghether. sometimes the "i need to do this every x minutes " paradigm can be changed if someone else looks at it.
Lowell
March 2, 2010 at 9:42 am
I've had what may be a similar issue. Same basic setup: a SQL Agent job that calls a SSIS package, but my job only runs once a day.
The job kicks off at 2 AM and normally takes about 5 minutes to complete, but what will happen occasionally is that the next morning the job is still "Executing." In other words, the job has started, but it never completes the first step (which in this case is to UnZip a file); in fact it doesn't look like it even starts the first step, it just says "Executing" and stays there -- no errors, nothing in any logs, etc.
If I stop the job and restart it, it always completes fine.
The only thing resembling a "fix" that I've found is to restart the SQL Agent service. If I do nothing, it appears that the issue will continue to happen somewhat frequently (a couple of times a week). Once I restart the Agent service, the problem usualy goes away for a couple of weeks or months, but it will usually happen again eventually.
Not very scientific, but I haven't had any breakthroughs. Anyone else had similar troubles with an Agent job?
March 2, 2010 at 10:08 am
this job runs a package that links two different applications together.
History:
we have users that create case in one system, this system allocates them a case ID, these cases also have to be created in the second system. at this point the users were manually entering the case id they got from the first system. as you can imaging we were getting alsorts of miss matching case id's caused by human error.
i wrote a combination of SQL, vb script and ssis functionality that at the point of creation in the primary system the package goes and creates the record in the second system and updates both systems with the id's for the corresponding cases in the other system.
the agent runs the package every 3 minutes because when the user creates the case they are waiting on the ids coming through from the second system so they can proceed to the next step on the case management.
its not ideal, but it dose solve a big problem we had with miss matching cases in both systems (especial when billing them.)
the package has to run frequently because the users are waiting on the ids coming through, so you can imagine the problems when this package stops working as expected, it dosent fail so i dong get an alert but it also dosent run so users are sitting waiting until i am alerted which usually means the phone ringing.
this dosent happen allot, every 2-3 weeks, but its the fact i dont know its happening thats the killer.
🙁
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply