Why am I getting a Backup alert

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

use msdb

Go

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.

01

Just update the “stop_execution_date” = CURRENT_TIMESTAMP

use msdb

Go

update sysjobactivity

set stop_execution_date = CURRENT_TIMESTAMP

where session_id = 6036 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

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