February 24, 2009 at 12:41 pm
Im not sure if this belongs under replication of administering. My question is mostly one concerning scheduling the jobs.
I am using Snapshot replication. I have the publisher on one server and the distributor is on the subscriber server. The distributor also pulls the subscription from the publisher. So basically I have 2 jobs. The first job is the Snapshot job. It is scheduled to start at 2:00 am. Sometimes it runs for 45 minutes, sometimes it runs for 2 hours and 45 minutes. At 5:00 am the distribution job starts. It is fairly consistent as far as it usually runs between 2 and 2 and a half hours. Problem is occasionally the replication database is not finished replicating until 7:30 the users are complaining. Sometimes to make matters worse they are on the publication database and start snapshot job to run past the 5:00 am start time of the distribution job and replication fails for the day.
There is a lot of time wasted though between when the first job finishes and the second job begins. So I want to set up the distribution job to finish as soon as the snapshot job is complete.
Currently I am thinking about using a Maintenance Plan to call the first job and then the second job.
An associate gave me a ration and said that Maintenance Plans are for scheduling database maintenance, and that I should script a job to combine the steps of the other two instead.
He thinks I can just cut and paste the steps of the other two jobs into steps for the new job.
Anyone else use maintenance plans to schedule other types of jobs besides actual maintenance tasks? Isnt that why they have modules for calling jobs from the agent, or T-SQL? So you can use it kind of like the old DTS Packages?
February 24, 2009 at 4:14 pm
sp_start_job is your friend!
* Noel
February 24, 2009 at 4:54 pm
Oh SQLServer and its stored procs....
So to run two jobs consecutively would I do something like this?
USE msdb ;
GO
EXEC dbo.sp_start_job N'Job_1' ;
GO
EXEC dbo.sp_start_job N'Job_2';
GO
Or would I put each execution in its own individual Step in the job?
February 24, 2009 at 4:59 pm
Louis Kapp (2/24/2009)
Oh SQLServer and its stored procs....So to run two jobs consecutively would I do something like this?
USE msdb ;
GO
EXEC dbo.sp_start_job N'Job_1' ;
GO
EXEC dbo.sp_start_job N'Job_2';
GO
Or would I put each execution in its own individual Step in the job?
sp_start_job is ASYNCHRONOUS!
so at the end of each job you call the next!
Hope is clear
* Noel
February 24, 2009 at 5:01 pm
Not really. I dont want to call them manually. I want to script them to run consecutively.
February 24, 2009 at 5:31 pm
No I dont think it is my friend...
I tried to use it to run two jobs and for some reason the second one starts before the first one is even finished.
I tried scripting it as above, and I also tried puting each lines in a separate job step using the job wizard.
What am I missing?
February 24, 2009 at 7:47 pm
Louis Kapp (2/24/2009)
No I dont think it is my friend...I tried to use it to run two jobs and for some reason the second one starts before the first one is even finished.
I tried scripting it as above, and I also tried puting each lines in a separate job step using the job wizard.
What am I missing?
you just add a last step on the first job that executes this sp which will invoke the "next" job.
... repeat as needed.
Is that clear enough ?
* Noel
February 25, 2009 at 12:12 pm
That's too obvious.
Thanks.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply