Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

SQL Server Job Agent – Invoke job sequence.

Posted by Simon Cho on 06/11/2014

1. [SQLAgent – Job invocation engine] : execute it based on active schedule and job at scheduled time.

 

exec sp_executesql N'UPDATE msdb.dbo.sysjobactivity SET run_requested_date = DATEADD(ms, -DATEPART(ms, GetDate()),  GetDate()), run_requested_source = CONVERT(sysname, @P1), queued_date = NULL, start_execution_date = NULL, last_executed_step_id = NULL, last_executed_step_date = NULL, stop_execution_date = NULL, job_history_id = NULL, next_scheduled_run_date = NULL WHERE job_id = @P2 and session_id = @P3',N'@P1 int,@P2 uniqueidentifier,@P3 int',1,'676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB',14

 

2. [SQLAgent – Job Manager] : Update start time. Check Permission. Get Jobstep. And execute each step

exec sp_executesql N'DECLARE @startExecutionDate DATETIME SET @startExecutionDate = msdb.dbo.agent_datetime(@P1, @P2) UPDATE msdb.dbo.sysjobactivity SET start_execution_date = @startExecutionDate WHERE job_id = @P3 and session_id = @P4',N'@P1 int,@P2 int,@P3 uniqueidentifier,@P4 int',20140611,144430,'676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB',14

exec sp_executesql N'EXECUTE msdb.dbo.sp_sqlagent_has_server_access @login_name = @P1',N'@P1 nvarchar(128)',N'sa'

exec sp_executesql N'EXECUTE msdb.dbo.sp_help_jobstep @job_id = @P1',N'@P1 uniqueidentifier','676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB'

EXECUTE @retval = sp_verify_job_identifiers '@job_name',
                                              '@job_id',
                                               @job_name OUTPUT,
                                               @job_id   OUTPUT,
                                              'NO_TEST'

--Execute each step

3. [SQLAgent – TSQL JobStep(Job 0x…) or SSIS JobStep(Job 0x…)] : Execute each step.

4. [SQLAgent – Job Manager] : Update job history and status.

exec sp_executesql N'EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = @P1, @step_id = @P2, @sql_message_id = @P3, @sql_severity = @P4, @run_status = @P5, @run_date = @P6, @run_time = @P7, @run_duration = @P8, @operator_id_emailed = @P9, @operator_id_netsent = @P10, @operator_id_paged = @P11, @retries_attempted = @P12, @session_id = @P13, @message = @P14',N'@P1 uniqueidentifier,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int,@P11 int,@P12 int,@P13 int,@P14 nvarchar(4000)','676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB',1,0,0,1,20140611,144430,0,0,0,0,0,14,N'Executed as user: NT AUTHORITY\NETWORK SERVICE. The step succeeded.'

5. [SQLAgent – Update job activity] – Update next schedule.

exec sp_executesql N'DECLARE @nextScheduledRunDate DATETIME SET @nextScheduledRunDate = msdb.dbo.agent_datetime(@P1, @P2) UPDATE msdb.dbo.sysjobactivity SET next_scheduled_run_date = @nextScheduledRunDate WHERE session_id = @P3 AND job_id = @P4',N'@P1 int,@P2 int,@P3 int,@P4 uniqueidentifier',20140611,144440,14,'676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB'

6. [SQLAgent – Job Manager] – Update last run time

exec sp_executesql N'UPDATE msdb.dbo.sysjobservers SET last_run_date = @P1, last_run_time = @P2, last_run_outcome = @P3, last_outcome_message = @P4, last_run_duration = @P5 WHERE (job_id = @P6) AND (server_id = 0)',N'@P1 int,@P2 int,@P3 int,@P4 nvarchar(4000),@P5 int,@P6 uniqueidentifier',20140611,144430,1,N'The job succeeded.  The Job was invoked by Schedule 30 (test).  The last step to run was step 1 (step01).',0,'676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB'

exec sp_executesql N'EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = @P1, @step_id = @P2, @sql_message_id = @P3, @sql_severity = @P4, @run_status = @P5, @run_date = @P6, @run_time = @P7, @run_duration = @P8, @operator_id_emailed = @P9, @operator_id_netsent = @P10, @operator_id_paged = @P11, @retries_attempted = @P12, @session_id = @P13, @message = @P14',N'@P1 uniqueidentifier,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int,@P11 int,@P12 int,@P13 int,@P14 nvarchar(4000)','676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB',0,0,0,1,20140611,144430,0,0,0,0,0,14,N'The job succeeded.  The Job was invoked by Schedule 30 (test).  The last step to run was step 1 (step01).'

Done.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: