sp_start_job - Don''t know how to explain this
-
Greetings all;
I just got finished creating a stored procedure that creates and starts x number of jobs on the SQL Server Agent Job queue. Without getting into a very long detailed explanation of why this was done, I can say the process does work. The stored procedure that creates the jobs, when runs from EM the jobs start and then drop off when they finish (I create 7 jobs on the fly all at once and start them using sp_start_job). However when I schedule the job that creates all of the on-the-fly jobs it appears the on-the-fly jobs indicate they finish at the exact same time. Am I making myself clear here?
I'm at a loss trying to identify why this is happening.
Kurt
DBA
RHWI Inc.
Poughkeepsie, NY
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NYhttp://www.linkedin.com/in/kurtwzimmerman
-
1 - Do the jobs do their job when starting the master proc as a job?
2 - Who is the owner of that job, the procs and under what account does the agent run?
-
1) yes. The master job starts all spawned jobs and all of the spawned jobs do run successfully.
2) The spawned jobs run under the same account as the master job is running.
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NYhttp://www.linkedin.com/in/kurtwzimmerman
-
Who is the owner of the master job?
Under what account does the agent run?
-
The Master job & agent jobs run under the same domain sysadmin account.
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NYhttp://www.linkedin.com/in/kurtwzimmerman
-
What do the jobs do?
How do you know something has gone wrong?
Do you have any error message?
-
I'm guessing you are missing the point of my original issue. There is no major problem. Some background....
I started with a stored procedure that ran single-threaded. A necessary modification was made which cause the stored procedure to take almost 4 time as much time. I was able to break up the process into 7 different pieces which now can run concurrently. The main job generates these individual jobs with unique parameters to be passed for each job.
When I call the main stored procedure from EM the 7 jobs get created, run and end. Execution times are between 2 to 3 hours per job.
However, when I schedule the main stored procedure from the job queue ALL of the jobs terminate exactly at the same time. Despite of that all of the work the 7 jobs are required to do are performed without problems.
So that is the issue... I'm looking for some explanation why the individual jobs don't terminate when they finish... but when the last job finishes...
I hope I have explained this.
Right now I have the job running in production without issue (other than what I described). I was hoping to find some sort of explanation why it runs that way.
Kurt
DBA
RHWI
Poughkeepsie, NY
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NYhttp://www.linkedin.com/in/kurtwzimmerman
-
No idea on how to explain this.
Can you run profiler at night while that process is started / running and ends? Maybe that'll give some more information on what happens.
-
Thanks for your help. I am glad that I've got the process to work and I was able to squeeze a proces that would have taken 24 hrs to run down to just over 3 hrs.
Kurt
DBA
RHWI, Inc
Poughkeepsie, NY
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NYhttp://www.linkedin.com/in/kurtwzimmerman
-
So how did you solve the problem?
-
I didn't. The job has been running for almost 1 week. Time to move on to other projects.
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NYhttp://www.linkedin.com/in/kurtwzimmerman
-
Ya the old if it ain't broke, don't fix it
.Thanks for the follow up.
-
Here is a little side note that I'd like to share with you. First some more background. Since the jobs that I'm creating on the fly are only "temporary", I wanted the ability to remove them after they finished. Since I write my own history file I really don't care about keeping them around.
One of the first problems that manifested itself by having all of the jobs terminate at exactly the same time is the Delete step would fail. I use this sproc to delete the job.
sp_delete_job. All seemed to be fine in development... then I moved it to production. When I found the jobs executing at exactly the same time I'd get a dead-lock as the job steps were trying to delete themselves. To alleviate this problem I put in a delay (based upon the job creation loop in the queue sproc). This worked perfectly and found all of teh jobs properly dropping off the queue.
In the back of my mind I will continue to find ways to tidy up this process. Until then I will let it do it's thing.
Kurt
DBA
RHWI
Poughkeepsie, NY
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NYhttp://www.linkedin.com/in/kurtwzimmerman
-
Thanks for the feedback.
-
Kurt,
I am assuming you are using sp_add_job in your stored procedure to create the jobs. You can add a parameter to this procedure, @delete_level = x. The value for x can be 0 (never), 1 (on success), 2 (on failure), or 3 (always). Doing this, you don't need a job that then deletes the jobs when they are finish.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply