November 16, 2016 at 2:32 am
Hi chaps,
1st post here, so be gentle with me 🙂
Got a bit of an odd one here that I'm scratching my head over. Got a few SQL servers running various SQL agent jobs and 99% of the time, these complete without any issue what so ever, but every now and then (I get a little bit lonely and you're never coming around :-D), the job will fail with:
Executed as user: domain\xxxxx. The process could not be created for step 1 of job 0xBE5FA266747AB344A3684CE0F962C6A2 (reason: Access is denied). The step failed.
So for example, I have several databases on a server that is being log shipped to another server. This is scheduled to run every 15 minutes and as mentioned above, completes 99% of the time, but that 1% will throw the error above, which in then causes about a dozen emails to flood in, because the job failed for all the log shipping jobs scheduled. Also, this happens on other servers that don't have log shipping and it will be the Ola Hallengren LOG backup job that will fail (and an email alert will come through), but 15 minutes later it all works again.
Any ideas on this one please?
Thanks.
November 16, 2016 at 4:04 am
Have you got any errors in SQL Server Logs or in System log in OS?
November 16, 2016 at 4:22 am
Hi Tazzman,
Nothing in System OS logs at the time of the job failure and it was logged in the application OS log as:
SQL Server Scheduled Job 'DatabaseBackup - USER_DATABASES - LOG' (0xBE5FA266747AB344A3684CE0F962C6A2) - Status: Failed - Invoked on: 2016-11-16 09:45:00 - Message: The job failed. The Job was invoked by Schedule 1019 (Transaction log backup). The last step to run was step 1 (DatabaseBackup - USER_DATABASES - LOG).
That was logged as:
Source: SQLSERVERAGENT
EventID: 208
SQL Server Log is blank for it.
SQL Server Agent Log is:
Message
[136] Job DatabaseBackup - USER_DATABASES - LOG reported: The process could not be created for step 1 of job 0xBE5FA266747AB344A3684CE0F962C6A2 (reason: Access is denied)
SQL Job History is:
Message
The job failed. The Job was invoked by Schedule 1019 (Transaction log backup). The last step to run was step 1 (DatabaseBackup - USER_DATABASES - LOG).
And then expand out the step, it is
Message
Executed as user: domain\xxxxx. The process could not be created for step 1 of job 0xBE5FA266747AB344A3684CE0F962C6A2 (reason: Access is denied). The step failed.
November 16, 2016 at 4:45 am
From what you have written I guess you job is trying to access backup file to restore it to another database.
Can you check whether the jobs are failing when restore job is trying to access a file which is not a fully completed backup?
Also check whether you have any jobs to copy the files to any other location.
November 16, 2016 at 4:47 am
"Access is denied" sounds like an operating system error. Maybe you're trying to copy files to a location you (ie the SQL Server Agent or proxy account) don't have access to? Strange that it doesn't happen all the time, though. Have you tried going to the Advanced tab and configuring an output file, so that you capture the full output of whatever process the job step runs?
John
November 16, 2016 at 4:56 am
From what you have written I guess you job is trying to access backup file to restore it to another database.
Can you check whether the jobs are failing when restore job is trying to access a file which is not a fully completed backup?
Also check whether you have any jobs to copy the files to any other location.
Hi Rechana,
The job doesn't fail for the restore process, nor for the copy process, just the backup process. If there is no file to copy or restore, SQL doesn't complain anyway, only when the threshold hasn't been met for restore / copy, then SQL tends to thrown a wobbly.
"Access is denied" sounds like an operating system error. Maybe you're trying to copy files to a location you (ie the SQL Server Agent or proxy account) don't have access to? Strange that it doesn't happen all the time, though. Have you tried going to the Advanced tab and configuring an output file, so that you capture the full output of whatever process the job step runs?
Hi John,
No, I haven't configured the advanced part for the full output, but generally access denied is access denied 🙂
For the error output I quoted in my previous post, this was for the Ola Hallengren Log backup job and happened this morning. This was for a server that isn't log shipping and is backing the tlog file up to a local drive
November 16, 2016 at 5:12 am
HBollah (11/16/2016)
I haven't configured the advanced part for the full output, but generally access denied is access denied 🙂
Mmmm... that's about as helpful as the government saying "Brexit means Brexit"! (I'm guessing from your knowledge of Bonnie Tyler lyrics that you may be British.) You're not likely to find out precisely what access to do was denied unless you capture the full output. And you never know - you might notice some other error that may have been causing the "access is denied".
For the error output I quoted in my previous post, this was for the Ola Hallengren Log backup job and happened this morning. This was for a server that isn't log shipping and is backing the tlog file up to a local drive
Sounds like you don't have access on the local drive, then. Although, as I said before, I don't understand why that's only happening some of the time. Again, get the whole output and see whether it reveals anything.
John
November 16, 2016 at 5:36 am
Thanks John, yes, British here. Brexit doesn't mean to much to me at the moment. Just lots he said / she said, blah blah blah 😀
Anyway, I've setup full output on the log shipping job to see what it comes back with. Interestingly enough though, the advance output is setup for the Ola Hallengren job, so had a look at the time it failed and there is no text file for it, just the SQLAGENT.OUT file which was updated at the time of the failure and I've already put up what the output of that was.
All very curious though.......
November 16, 2016 at 5:46 am
Can you get any of the steps to fail if you run them manually? You may have to use EXECUTE AS so that it runs in the same context as the SQL Server Agent account or proxy.
John
November 16, 2016 at 6:03 am
Because it happens only occasionally: could it be an existing file must be overwritten, which is already (or still) locked by another process?
Something like a backup that is created to a file that is currently active in a restore? Or to a file that a previous backup process did not close nicely (and thus still have locked)?
November 16, 2016 at 7:16 am
John Mitchell-245523 (11/16/2016)
Can you get any of the steps to fail if you run them manually? You may have to use EXECUTE AS so that it runs in the same context as the SQL Server Agent account or proxy.
I've got enough to be getting on with and have setup the additional output. Typically, I only set it up on one of the servers and the other server failed because I hadn't set it up on there :doze: It is bound to happen between now and tomorrow, so will probably report back in with the output
Because it happens only occasionally: could it be an existing file must be overwritten, which is already (or still) locked by another process?
Something like a backup that is created to a file that is currently active in a restore? Or to a file that a previous backup process did not close nicely (and thus still have locked)?
Unfortunately not. With log shipping or Ola's script, the file it generates is a new file that includes a time stamp in the name, so it wouldn't be overwriting an existing file.
November 16, 2016 at 7:28 am
If you discover it to be a timing issue, and the job step can be repeated to completion, then consider going under Advanced properties of the step and setting it to retry once more after x minute delay.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 16, 2016 at 7:32 am
Eric M Russell (11/16/2016)
If you discover it to be a timing issue, and the job step can be repeated to completion, then consider going under Advanced properties of the step and setting it to retry once more after x minute delay.
Thanks Eric. I did see that setting the other day and did think about enabling it, but alias, I'd be setting that up on a lot of jobs, as well as not fixing the initial issue 🙁
November 16, 2016 at 8:29 am
Is the backup happening to local disk or network attached storage?
If it is network attached storage, you could bug your network admin to see if there are any network issues at that time or possibly a SAN backup process that locked the folder?
Another thought: we had odd issues one time becuase one of our SQL Server Agent accounts got locked and it caused jobs to randomly fail. Possibly double check that the SQL Server Agent service isn't set to run with the same name as a different service account. A locked account usually gives a different error message (forget what offhand, but I have seen errors which don't accurately reflect what I am seeing).
Failing that, do you notice any similarities about when it fails? Like it fails every third Monday. If so, maybe check the windows event scheduler to make sure no other jobs are running at that time. It could be something easy like the team managing your DC is doing maintenance once every 2 months on the domain controller at the same time your job runs and the account is unable to authenticate.
Last thing I'd check is when the machine was last rebooted (the SQL box). I wonder if it is just pegged for resources so it cannot start a new process.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 17, 2016 at 7:00 am
bmg002 (11/16/2016)
Is the backup happening to local disk or network attached storage?If it is network attached storage, you could bug your network admin to see if there are any network issues at that time or possibly a SAN backup process that locked the folder?
Another thought: we had odd issues one time becuase one of our SQL Server Agent accounts got locked and it caused jobs to randomly fail. Possibly double check that the SQL Server Agent service isn't set to run with the same name as a different service account. A locked account usually gives a different error message (forget what offhand, but I have seen errors which don't accurately reflect what I am seeing).
Failing that, do you notice any similarities about when it fails? Like it fails every third Monday. If so, maybe check the windows event scheduler to make sure no other jobs are running at that time. It could be something easy like the team managing your DC is doing maintenance once every 2 months on the domain controller at the same time your job runs and the account is unable to authenticate.
Last thing I'd check is when the machine was last rebooted (the SQL box). I wonder if it is just pegged for resources so it cannot start a new process.
Hi bmg002,
The log shipping jobs are backing up to a folder share on a different server, whilst the Ola script backups locally.
The servers are all set to use their own service account for the SQL engine and the SQL Agent, so it won't be that 🙁
There is no similarities to this either, which makes it more troublesome to diagnose. The jobs have failed once yesterday evening and once today, but on different servers, whilst a different Ola job failed on a completely different server.
To everyone else (especially John). The advanced logging was enabled and the job failed, got to look at the timestamp of when it failed and it hasn't even been recorded in the log file. The failure happened at 13:30pm and the log file has entries for 13:15 and 13:45 :angry:
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply