July 11, 2017 at 11:08 am
I have a sql agent job that runs every 5 minutes. It executes a stored proc that only takes a few seconds to run. Periodically, I'll get the following error:
SQLServerAgent Error: Request to run job 4167768C-XXXX-XXXX-XXXX-1BB549FDXXXX (from User sa) refused because the job is already running from a request by XXXXXXXX XXX (XXXXXXXX_X). [SQLSTATE 42000] (Error 22022). The step failed.
I get that sql agent thinks the job is still running. The odd thing that I'm trying to sort out is the "request by XXXXXXXX XXX (XXXXXXXX_X)". My understanding is that this should be a user but no user by the indicated name exists on my server and I can't find any other object by that name. Any thoughts on where it's getting this name from?
Thanks.
July 11, 2017 at 4:14 pm
Samwell - Tuesday, July 11, 2017 11:08 AMI have a sql agent job that runs every 5 minutes. It executes a stored proc that only takes a few seconds to run. Periodically, I'll get the following error:SQLServerAgent Error: Request to run job 4167768C-XXXX-XXXX-XXXX-1BB549FDXXXX (from User sa) refused because the job is already running from a request by XXXXXXXX XXX (XXXXXXXX_X). [SQLSTATE 42000] (Error 22022). The step failed.
I get that sql agent thinks the job is still running. The odd thing that I'm trying to sort out is the "request by XXXXXXXX XXX (XXXXXXXX_X)". My understanding is that this should be a user but no user by the indicated name exists on my server and I can't find any other object by that name. Any thoughts on where it's getting this name from?
Thanks.
How is this job being started every 5 minutes? Fired from an application or some other process using sp_start_job? If it's run from a schedule, you'd normally see something like "already running from a request by Schedule xxx"
So it looks like sa tried to run it but that second xxxxxx had already started it. And if could be whoever xxxx has access and permissions to execute sp_start_job. If that's the case, you may want to check the role members in msdb as well as sysadmins as that could narrow things down.
Members of SQLAgentUserRole and SQLAgentReaderRole can only start jobs that they own.
Members of SQLAgentOperatorRole and sysadmins can start all local jobs including those that are owned by other users.
Sue
July 11, 2017 at 4:36 pm
Adding to Sue's response, if user XXXX doesn't explicitly exist in your database, but is a windows user, they could be a member of an AD group that has permission on the database.
And, if I am not mistaken, being an Administrator on the physical machine can result in being an administrator on the SQL Instance if BUILTIN/ADMINISTRATORS is a sysadmin on the SQL instance.
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.
July 12, 2017 at 6:18 am
The job is run from a schedule and the user is Schedule XXX but that number doesn't correspond to any schedule in dbo.sysjobschedules.
July 12, 2017 at 6:50 am
Samwell - Wednesday, July 12, 2017 6:18 AMThe job is run from a schedule and the user is Schedule XXX but that number doesn't correspond to any schedule in dbo.sysjobschedules.
It's normally a number - yours is a persons name instead? Or was it a number? I'm not clear on what you mean when you say "the user is Schedule XXX". Nonetheless, the schedule is there. Maybe try checking by schedule names. Check the properties of the job, go the the schedules page. Each schedule will have a name. You can find the schedule id for the name of each schedule by querying sysschedules.
Did you also query sysjobschedules by job ID? And did you check the job properties and make sure you got the schedules associated with that job?
Sue
July 12, 2017 at 7:00 am
This was removed by the editor as SPAM
July 12, 2017 at 8:38 am
Samwell - Wednesday, July 12, 2017 7:00 AMThe request by is Schedule ### (Schedule_1) but this name/number doesn't correspond to any schedule for the job. I can't find any reference to any schedule with that name.or number. I've queried dbo.sysjobschedules and dbo.sysschedules and there is no reference to anything similar to the request by.
Check the error again. It would be "request by Schedule 00 (NameOfSchedule)"
where 00 would be the ID number for the schedule. The name of the schedule is in parenthesis after the schedule number.
Sue
July 12, 2017 at 11:54 am
Another thing to watch, if you are using an SSIS catalog (might apply to the non-catalog method too), the job doesn't need to reside on the same server as the SSIS catalog.
We had a job on our live SSIS server that had a job step that pointed to the test SSIS catalog. Didn't notice for a while until somebody had updated the test version to include extra columns in a table and the job failed due to metadata being bad.
You may just be looking at the wrong SQL instance for the job.
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply