February 22, 2018 at 12:51 pm
All,
One of our SQL jobs missed the scheduled run and I couldn't figure out why. SQL job is scheduled to run daily and shouldn't skip this unless it is either running or might have run long enough due to a previous job run that caused it to skip. I've reviewed the logs both agent and sql error log but couldn't find anything fishy. Also, no changes happened on the server as report by the IT team, job is enabled and it has not been disabled as well as the last modified date shows 02/16 and the actual issue surfaced around 02/21 till date. I also checked other SQL jobs if this would be triggered through another SQL job but that is not the case either. Is there a way I can track what would have caused it to miss the execution. Also, moving forward what would be the mechanism to catch hold of such one of situations. Can I run a SQL profiler trace and what all events and sessions I should capture in order to get hold of the culprit.
Please advise.
February 22, 2018 at 1:53 pm
So the job ran every time up until 2/21? And you confirmed that the job wasn't already running at that time? Is the schedule for the job enabled (disabling the schedule won't update the modified date for the job)?
February 22, 2018 at 2:10 pm
SQLPirate - Thursday, February 22, 2018 1:53 PMSo the job ran every time up until 2/21? And you confirmed that the job wasn't already running at that time? Is the schedule for the job enabled (disabling the schedule won't update the modified date for the job)?
I already verified those details. As mentioned the SQL job is enabled. Is there a way if I can find out if the SQL job was disabled by some user at that time..since you mentioned that disabling a job won't affect the modified date which makes sense because you are not making changes to it. Can I find this info in the default trace file or any other way of grabbing that info.
February 22, 2018 at 2:29 pm
ffarouqi - Thursday, February 22, 2018 2:10 PMSQLPirate - Thursday, February 22, 2018 1:53 PMSo the job ran every time up until 2/21? And you confirmed that the job wasn't already running at that time? Is the schedule for the job enabled (disabling the schedule won't update the modified date for the job)?I already verified those details. As mentioned the SQL job is enabled. Is there a way if I can find out if the SQL job was disabled by some user at that time..since you mentioned that disabling a job won't affect the modified date which makes sense because you are not making changes to it. Can I find this info in the default trace file or any other way of grabbing that info.
Disabling the job and disabling the schedule are two different things, which is why I asked. The job can be enabled while the schedule that runs it is disabled. Disabling the actual job will update the modified date.
February 22, 2018 at 2:41 pm
SQLPirate - Thursday, February 22, 2018 2:29 PMffarouqi - Thursday, February 22, 2018 2:10 PMSQLPirate - Thursday, February 22, 2018 1:53 PMSo the job ran every time up until 2/21? And you confirmed that the job wasn't already running at that time? Is the schedule for the job enabled (disabling the schedule won't update the modified date for the job)?I already verified those details. As mentioned the SQL job is enabled. Is there a way if I can find out if the SQL job was disabled by some user at that time..since you mentioned that disabling a job won't affect the modified date which makes sense because you are not making changes to it. Can I find this info in the default trace file or any other way of grabbing that info.
Disabling the job and disabling the schedule are two different things, which is why I asked. The job can be enabled while the schedule that runs it is disabled. Disabling the actual job will update the modified date.
Apologies I misread that in a hurry. However, both of them are enabled. I will revise my question is there a way to find out if someone disabled the schedule and then re-enabled it as it won't affect the modified date.
February 22, 2018 at 3:04 pm
ffarouqi - Thursday, February 22, 2018 2:41 PMSQLPirate - Thursday, February 22, 2018 2:29 PMffarouqi - Thursday, February 22, 2018 2:10 PMSQLPirate - Thursday, February 22, 2018 1:53 PMSo the job ran every time up until 2/21? And you confirmed that the job wasn't already running at that time? Is the schedule for the job enabled (disabling the schedule won't update the modified date for the job)?I already verified those details. As mentioned the SQL job is enabled. Is there a way if I can find out if the SQL job was disabled by some user at that time..since you mentioned that disabling a job won't affect the modified date which makes sense because you are not making changes to it. Can I find this info in the default trace file or any other way of grabbing that info.
Disabling the job and disabling the schedule are two different things, which is why I asked. The job can be enabled while the schedule that runs it is disabled. Disabling the actual job will update the modified date.
Apologies I misread that in a hurry. However, both of them are enabled. I will revise my question is there a way to find out if someone disabled the schedule and then re-enabled it as it won't affect the modified date.
Unfortunately, that information isn't logged by default.
Other questions:
Was SQL Agent running at the time? (it probably was, but should be asked)
Did you confirm that the schedule isn't set to skip that day for some reason or that it has an end date set that would've caused it to miss?
When you look at the job history for the job can you confirm it was previously invoked by the schedule?
All the other jobs are running as scheduled at the right time(s)?
February 22, 2018 at 9:37 pm
SQLPirate - Thursday, February 22, 2018 3:04 PMffarouqi - Thursday, February 22, 2018 2:41 PMSQLPirate - Thursday, February 22, 2018 2:29 PMffarouqi - Thursday, February 22, 2018 2:10 PMSQLPirate - Thursday, February 22, 2018 1:53 PMSo the job ran every time up until 2/21? And you confirmed that the job wasn't already running at that time? Is the schedule for the job enabled (disabling the schedule won't update the modified date for the job)?I already verified those details. As mentioned the SQL job is enabled. Is there a way if I can find out if the SQL job was disabled by some user at that time..since you mentioned that disabling a job won't affect the modified date which makes sense because you are not making changes to it. Can I find this info in the default trace file or any other way of grabbing that info.
Disabling the job and disabling the schedule are two different things, which is why I asked. The job can be enabled while the schedule that runs it is disabled. Disabling the actual job will update the modified date.
Apologies I misread that in a hurry. However, both of them are enabled. I will revise my question is there a way to find out if someone disabled the schedule and then re-enabled it as it won't affect the modified date.
Unfortunately, that information isn't logged by default.
Other questions:
Was SQL Agent running at the time? (it probably was, but should be asked)
Did you confirm that the schedule isn't set to skip that day for some reason or that it has an end date set that would've caused it to miss?
When you look at the job history for the job can you confirm it was previously invoked by the schedule?
All the other jobs are running as scheduled at the right time(s)?
We'd similar issues as you posted, but what happened to us, unfortunately we'd changed the order of the sequence of steps. This was caused our job to skip the executions for particular steps. Later we'd changed it to default, it was fine.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply