We use SQL server central management server (Master server) to manage all SQL instance centrally, in order to apply policy, update, query entire environment form one center location, other than login each server. We create all job in the master server and push to all target server, but job run individual server in different schedule. So we create one additional job (schedule job) individual server to call actual job. Many time we get backup alert through backup job, which check all backup jobs status if any backup job is not run based on schedule it throw alert.
But when I checked the schedule job history it was ran on time, but when I checked actual job history it didn’t run, no failing encounter just not running based on schedule. Once I tried to ran actual backup job manually its work, so I tried to ran schedule job again to see if it called actual backup job or not. No it did not calling the job, so I found the below issue:
To see job activates we need “Job_ID” by query on sysjobs_views
select * from sysjobs
where name = ‘job name’
Then find job activity by using:
select * from sysjobactivity
where job_id= ‘your job_ID’
If you identify “stop_execution_date” value is null that means this the reason the actual backup job is not running. Because when schedule job calling actual job based on job schedule it look through last ‘stop_execution_date’. When they can’t identify the time because of null value it’s skipping the actual operation.
Just update the “stop_execution_date” = CURRENT_TIMESTAMP
set stop_execution_date = CURRENT_TIMESTAMP
where session_id = #### AND job_id = ‘your job_ID’
Now ran the schedule job to check if it is calling the actual job. Yes it did work.
-By MD Ullah