May 2, 2014 at 7:40 am
Hello everyone,
We have had an interesting issue with one of our production servers lately and I cant really put my finger on why its happening.
Scenario is as follows: We have a job that has multiple job steps.
The step for the job reads like this: exec ssrsJob_spBillingSummaryCurrentPeriod NULL
When you execute this step, the job just hangs, no processing takes place. The job does not fail or go to the next step, just hangs there and racks up the run time.
When I execute the exact same code from managements studio in a query window, the code executes just fine. and the SP is done in a matter of seconds.
Any ideas as to why I cannot run this in a job?
May 2, 2014 at 8:14 am
Can you execute the exact same code from managements studio in a query window with the SQL Agent service account?
May 2, 2014 at 8:19 am
Yes, job executing as SA. Running the SP standalone as SA as well. Job doesnt work, standalone does.
May 2, 2014 at 10:53 am
Have you uses sp_who/sp_who2/sp_whoisactive or queried sys.dm_exec_connections/sessions or run a trace to see what the SQLAgent session is doing?
Based on the fact that the sp starts with ssrs* I'd guess that this is doing something with reporting services, what is it and does the SQLAgent user have the rights on the SSRS server to do it?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 2, 2014 at 12:33 pm
It actually has nothing to do with reporting services. this job just populates tables that are then used by reporting services in another job.
May 2, 2014 at 12:43 pm
Okay. Like I said, that was a guess based on the name. I'm not surprised I guessed wrong.
I just thought of something else. The connection settings can have an impact. Check out Erland Sommarskog's excellent article, "Slow in the Application, Fast in SSMS", to see how this works.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 2, 2014 at 12:48 pm
I will check that out this weekend and report back....thanks for the article!
May 2, 2014 at 1:06 pm
aaron.floyd (5/2/2014)
Yes, job executing as SA. Running the SP standalone as SA as well. Job doesnt work, standalone does.
Wait what's the service account for sql agent service?
Login with that account via ssms and see if u can execute the SP
It may be permissions issue.
May 2, 2014 at 1:09 pm
I have, it runs as SA in SSMS. The Job runs as SA as well. The job would fail if there were invalid permissions. That isnt the case here, it just hangs.
May 2, 2014 at 1:47 pm
aaron.floyd (5/2/2014)
I have, it runs as SA in SSMS. The Job runs as SA as well. The job would fail if there were invalid permissions. That isnt the case here, it just hangs.
Does this SP interacting with a file system?
Just FYI:
Sa cannot be a service account.
Sql server configuration manager will tell you which account(localsystem or domain account) is assigned as a service account.
May 5, 2014 at 10:11 am
no, simply creating rows that get inserted into another table in the DB, pretty standard processing for SQL
May 5, 2014 at 10:11 am
anyone else have any ideas?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply