Job reported: Unable to terminate process (reason: Access is denied)
-
Using SQL Server 2016 SP1 running on VM a SQLAgent job is hanging on one step that executes an SSIS package most executions. The SQL Server Agent Error Log shows the subject line message with jobname, step, and process name inserted but no other useful information.
- -
The same package is executed by another job using different configuration file for another database without issue.
- -
The SP that the packages executes runs without issue if executed manually in SSMS.
- -
I ran DBCC CHECKDB against MASTER and MSDB with no problems reported.
- -
Anyone know what the problem is? Or where I can look to find more information?
- -
Mark D Powell - Mark D Powell - Thursday, August 24, 2017 1:58 PM
Is it always the same package which fails? What does the package do?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Adding to this, what does the "All Executions" report look like for that SSIS package (presuming you are using an SSIS catalog)?
Might not hurt to turn on verbose logging for 1 execution of it to see exactly what is going on. But before doing that, as Phil stated, letting us know what this package is doing will likely help. What I mean is, if this is doing a giant insert into a table (millions and millions of rows), it may just be that it takes a while to complete with the parameters in the one config file. If it is doing a lot of calculations, same thing.Are the differences in the configuration files any indicator as to why it is slow? Like is config file 1 causing it to pull 10 rows and it completes quickly while config file 2 causes it to pull 10 billion rows and thus it is slow?
Also, how much memory does the physical SSIS server have free? We have a process that normally takes 5 minutes or less to complete that we had running for 8 hours because the server ran out of memory and had to page to disk. Reduced SQL's max memory for instances that didn't need it as high and the process has so far not gone over that 5 minute mark (knock on wood).
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. -
I would also be interested in hearing what this step in the package does.
I have encountered a similar problem whereby a DML operation wasn't committed and blocked a session wanting access to that data.
The fact the package is hanging and reporting this error gives me the idea that it is simply waiting for something that results (perhaps) in a timeout but the question is naturally, what?
-
All,
1- I have asked the developer for a detailed but still high level description of what the packages does. I will post back when I get it. Last night the job step successfully completed which as I mentioned happens some of the time.
2- We do not use the SSIS catalog database as we migrated to this new install a few months back from SQL Server 2012. I will inquire if this package exists on the old system or not.
- -
I appreciate your consideration of this issue.
- -
Side question. It appears easy for me to create a SSIS Catalog database, but where can I read on what effect this has on our existing packages and jobs?
- -
Mark D Powell - Mark D Powell - Friday, August 25, 2017 7:47 AM
Not sure where you can read about it, specifically, but I can assure you that the mere creation of an SSISDB catalog DB will have zero effect on the packages and jobs you already have in place.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Here is the SSIS package functionality as provided by the developer. I have asked for a brief description of what each box does.
-
Here is the description of the diagram boxes:
There is a procedure under everytask as shown in diagram below.
All the procedure are pullingdata from one schema(OBJ) applying business logic and writing it to multipletemp table and final write to DBO tables.
This is been done for all theprocedure.
The linkage shows that a taskwill wait for all the previous task to execute.
We are not using any linkedserver or writing/reading from any flat file in all these procs
- -
I hope all this helps someone think of something because my internet searches have not found anything useful. One reboot which we have done and another on DCOM setting which I looked at.
- -
Mark -
Ok, so my next thought is what is different between the 2 configuration files you are using?
My GUESS is that they are pointing to different SQL Instances or databases. If this is the case, does one have a LOT more data in it than the other? I am wondering if maybe one config file is moving 10 rows of data while the other is moving 100,000,000 rows of data.It looks like "Change Point" and "CE SERIES POP" will run twice if I am looking at that package correctly. It will fire when "Refresh VBO" completes and then fire again when "Refresh Claim Events" finishes. This seems odd to me, but it might be intentional OR there may be more logic behind that we cannot see from the screenshot.
We had that with one of our SSIS packages and we found that things that using sequence containers makes the control flow easier to follow and understand. It also makes it easier to have multiple things running at the same time.
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. -
SSCertifiable, I have asked the developer about your concerns on some of the routines running twice.
- -
Here is the developer response >>There is an AND condition to the“change Point”.
Change point will run only if‘refresh VBD’ and ‘Refresh Claim Events’ runs
So, No "ChangePoint" and "CE SERIES POP" will NOT run twice
<<
-
Good to know.
Brings up the next question though - what is different between those 2 configuration files?Also, was re-reading the initial post... how does the JOB call the SSIS package in each configuration file?
With the second, it sounds like it is running a stored procedure. What does the stored procedure do prior to running the SSIS package?
And who is the job step and stored procedure set to run as?The error "Access is denied" makes me think that it is either running the job step as a specific login who doesn't have proper permissions or the SP has something in there defining who it is executing as.
And another question - looking at the SSIS package, it looks like it is just doing a data transfer (ETL or ELT type work), where is the step that is trying to terminate a process? is that part of the stored procedure?
I think a lot of my initial questions missed the fact that this appears to be a permission related thing. That is why when you run the SP manually it executes no problem - you are running it as somebody who has sysadmin permissions, correct? I expect the job is set to execute as a login who doesn't have proper permissions to terminate processes.
Or does the SSIS package try to terminate some 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. -
The job runs at the SQL Agent user which is the same as the database server user and the user is a System Administrator on the box.
- -
The job termination appears to be either part of SSIS or the SQL Agent itself and is not part of the package or SP code. I have seen the same error issued twice on the nightly backup job though in both instances the SSIS related job ran over and took the error. In the other runs the backup completed successfully even when the SSIS job failed though it was slow on those nights.
- -
The job shows as running successfully 3 days in a row, which I think is a recent record. This past weekend was Windows patch weekend so who knows if the updates will have any effect on the issue.
- -
Mark -
If you execute the stored procedure as the SQL Agent user, does it execute successfully or do you get that same error?
Since it has run successfully 3 days in a row, hopefully it was some odd windows update that got applied. It would still be nice to know exactly which update fixed it. With your windows patches that were applied, were any of them related to SQL Server or were they strictly Windows patches?
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. -
I will have to inquire on executing the SP as the SQL Server user. All of the SP execute fine when executed as the Application user.
-
I would expect, since it is getting "access is denied" errors that the problem is with a specific account.
But I suppose if things are running now, it might be that the windows updates fixed the problem. In a way, I hope it was windows updates because then the problem is resolved. But I also kind of hope it isn't because it makes the problem more interesting.
Is your SQL Server Agent Service account an AD account? If so, may not hurt to ask your AD admin if the account was locked out during the time you got those errors?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 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply