June 22, 2015 at 12:59 pm
I'm working on a process that will restore the databases on 3 development servers from their respective production environment counterparts. The process that does this will have to exist on just 1 server, due to reasons I won't bother going into here. Just trust me, the process, be it an agent job or ssis or whatever, has to be in 1 place.
So, on each of the 3 dev servers, let's call them A, B, and C, I already have SQL Agent jobs that restore the databases and re-build permissions.
Now I need to create something on A that will run its own Agent job, as well as the jobs on B and C.
I tried it via t-sql script, SSIS package, and Agent job and keep hitting the same issue... the jobs get fired off and return success immediately, keeping my process moving. I need whatever I go with for the overall process to wait until the restore jobs are completed.
In SSIS, I tried setting the flows to Completion rather than Success. It didn't change the behavior at all.
I tried an Agent job, but the cal returns success and goes to the next step. It's evaluating whether or not the job successfully started and moving on.
I've considered using a while loop that looks at the last restore date and just loops aimlessly until the last restore date is within the last 5 minutes or something, but that's SO wasteful. There's got to be a better way.
June 23, 2015 at 10:05 am
When using sp_startjob its an asynchronus call, so it wont wait till the job is finished. The way I get round this when only using a SQL Agent job to control it all is something like this:
Step1: If the job is local to the server run sp_start_job
Step2: If the Job Is local to the sever loop querying msdb to check if the job has finished, break once complete
Step3: If the job is on a remote server, call a batch file that starts the job on a remote server using the sqlcmd command line
Step 4: if the job is on a remote server, call a batch file that runs sql that loops querying msdb on the remote server using sqlcmd to check if the job has finished, break once complete
Mix \ match \ Replicate the steps to suit your process logic.
The same can also be acheived in SSIS with the same principals
MCITP SQL 2005, MCSA SQL 2012
June 23, 2015 at 10:10 am
Code to start a job on a remote server:
sqlcmd -SDBSERVER -E -Q"EXEC msdb..sp_start_job @job_name = 'Run RestoreDB'"
Code to check if the remote job has finished:
sqlcmd -SDBSERVER -E -i D:\Scripts\CHECK_Finished.sql
Contents of the sql file:
DECLARE @jobactive int,
@jobname nvarchar(1000)
SET @jobactive = 1
SET @jobname = 'Run RestoreDB'
WHILE @jobactive = 1
BEGIN
IF EXISTS(SELECT 1 FROM msdb.dbo.sysjobs J JOIN msdb.dbo.sysjobactivity A ON A.job_id=J.job_id WHERE J.name=@jobname AND A.run_requested_date > DATEADD(HH,-2,getdate()) AND A.stop_execution_date IS NULL)
SET @jobactive = 1
ELSE
SET @jobactive = 0
END
MCITP SQL 2005, MCSA SQL 2012
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply