SQL 2008 SQL agent jobs hang. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

SQL 2008 SQL agent jobs hang.

Author

Topic

tross
Starting Member

20 Posts

Posted - 2011-03-22 : 09:27:00

we are seeing job that are run by the SQL agent showing no cpu time or disk IO.

what I'm seeing.
1) jobs start ( or the activity monitor shows started)
2) some jobs run to completion.
3) others show no cpu time or disk IO.
4) there is NO BLOCK jobs and the cpu time and/or disk i/o are zero (per sp_who2)

I'll stop the service and reboot the box
restart these jobs and they will run and complete. (99% of the time)

today:
I saw 12+ jobs hung. ( no cpu time)
1) stop the sql server and agent
2) rebooted
3) started 4 of the 12+ jobs
4) as 1 finished I started another
5) I finished 9 of these jobs then saw that 4 had no cpu time ( even after the were started 20 min earlier)
6) did step 1 & 2
7) restarted these jobs and now the running and some have completed.

what can I look at to see what the problem is??

some jobs look at other servers ( slq and third party)
some jobs look that tables in it's own database.

it's not the same jobs, the other day it was only 6 jobs that had this problem

system info
sql 2008
windows 2003 r2 sp2

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) Mar 29 2009 10:27:29 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

TIA

Tim

lappin
Posting Yak Master

182 Posts

Posted - 2011-03-23 : 05:37:56

Have you checked the job history for errors? If there are no errors you could run a trace while jobs are running.

Run a TSQL_Replay trace (http://msdn.microsoft.com/en-us/library/ms189604.aspx). Choose "Save to file". Run the jobs. When finished stop trace and then extract the SQL. (http://msdn.microsoft.com/en-us/library/ms180822.aspx)
From the extracted SQL, find chunks related to jobs which haven't ran and execute them individually. This might show errors.

tross
Starting Member

20 Posts

Posted - 2011-03-23 : 08:43:15

Of course, everything is completed today.
there are no hung/stalled jobs

>> checked the job history for errors

there are never any errors in the job history, only the green arrow, indicating that it is or should be running.

>> trace while jobs

Thanks I'll try this.

I'm not sure that there will be any errors in the sql.
one of the jobs only has the following :

SELECT * INTO dbo.EDIHDRDTL
FROM v_EDIHDR

I don't think I’ll find an error in this one.
but who knows, I’ve seen stranger things.

Tim

tross
Starting Member

20 Posts

Posted - 2011-03-24 : 16:24:33

the trace does not work for me.

when I ran the trace in the AM I got nothing.
the job start between midnight and 4:00 am,

they will run at night sometimes,
when I run they during the day they run ok, 99.9% of the time.

any other thoughts?

Tim

alexdeva7
Starting Member

2 Posts

Posted - 2011-03-28 : 13:05:09

The site SQL Reports has a great SQL tutorial. Highly recommended for people just getting started on SQL selects.It's very helpfuly for anyone .

SQL Reports
http://www.sql-reports.net/

Enjoy it !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources