June 1, 2010 at 12:51 pm
How do I troubleshoot when I see hung jobs?
I'm sure restarting sql services will help but is this the only way out?
Thanks everyone.
June 2, 2010 at 8:57 am
I'm trying to figure out how to get the server to normal state when I see hung jobs.
Any ideas?
June 2, 2010 at 10:35 am
Well I believe if you only restart the "SQL Server Agent" service that would work and still keep your databases running.
You'll have to examine the specific jobs that are hanging. Do they hang everytime they run? Are several jobs running at the same time? Do you receive any windows application or SQL Server Log errors?
June 2, 2010 at 10:46 am
If I run sp_who2, will I be able to identify what the job is doing? And depending on that, can I take action(killing the process)?
Say if there are 10 jobs which are in hung status, how do i figure out the actual culprit job?
June 2, 2010 at 11:06 am
What is your definition of "hung status"? Viewing sp_who2 will show you what processes are running, be it a SQL Agent job or otherwise. You will also see the wait-type the process is waiting on as well.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 19, 2017 at 1:32 pm
Shawn Melton - Wednesday, June 2, 2010 11:06 AMWhat is your definition of "hung status"? Viewing sp_who2 will show you what processes are running, be it a SQL Agent job or otherwise. You will also see the wait-type the process is waiting on as well.
Hello Team,
Since couple of weeks I am experiencing once weird issue on SQLServer 2016 sp1. We had migrated critical server from SQLServer 2008 R2 to SQLServer 2016 with sp1 by using side by side. we had migrated all the stuff like databases, logins,db mail,jobs etc.
We have around 150 jobs are deployed in sqlServer agent. At the time of database cutover (Saturday 5 AM ET) from SQL 2008 R2 to SQLServer 2016, few ssis jobs are behaving hung state. jobs neither success not failed as it was showing as executing state. Actually we found this kind of behavior only for ssis jobs (around 39 jobs) and remaining jobs are working fine(TSQL).
Interestingly we could see hung state over transaction log backup as well (since we had created trn backup job from maintenance plan which run every 1 hour). we had verified the event viewer logs, sql agent logs and error logs and everything and nothing captured in the logs. we had checked the server health status and everything is up and running fine (cpu 5%, memory 15%) and also verified the locking, blocking status and we couldn't find anything.
Once we restarted the sqlserver agent then these jobs working smoothly without any issue and also running successfuly in their schedules.
After a week,again same issue was repeated at same time (Saturday 5 AM ET),those set of jobs were gone to hung state.
Again we had verifed the logs, blocking bla bla etc. nothing captured and again restarted the sql agent service from configuration manager then it got resolved. Here our intention is it's a production server and users reported that if jobs not run then data would stale. Below log was for all hung jobs after restart agen.
Unable to terminate process 1208 launched by step 1 of job 0xB4CF917BAF53234796F42A38EC45B871 (reason: Access is denied)
Can anyone help me on this issue as it would grateful to us
Regards,
Chaithanya
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply