March 25, 2003 at 4:51 am
I have setup a number of single and multiple stepped jobs on one of the companys' SQL2000 Servers.
I have setup a maintainance scheme for one of the databases on the server. After a month without any problems it has started to fail and get stuck every time it runs. It is set to run when there is little or no activity on the server i.e. 11pm. When I check the job in the morning all the different jobs for the plan have the status "Executing Job Step '1". I cannot manually stop these jobs for hours after it has "run". The Error log for the plan shows that it excecuted and exited reporting success.
The second problem is other jobs I have scheduled to merely run DTS packages. These jobs also get stuck "Executing Step'1". I can run the DTS package myself, and by copying the job it will run for another few days.
Any input would be much appreciated.
Edited by - Mad_eddie on 03/25/2003 04:53:45 AM
March 25, 2003 at 7:24 am
The jobs are running with an schedulle you specify at that out, or you define an idle condition, and you are letting them run when the CPU is idle?
March 25, 2003 at 7:35 am
They Are scheduled to run on a daily basis when the business is closed and no-one is accessing the SQL Server.
March 25, 2003 at 9:36 am
But how did you create that schedulle?, did you specify to start at one specific time?
March 25, 2003 at 12:46 pm
When you see the 'Executing Step 1', have you ever 'Refresh'ed the jobs? (right click on the job or on JOBS in the drill-down tree) and select refresh.)
Has anything changed on the server? For example, are other maintenance jobs being done by the SysAdmin that might not have been done before?
Anything else change in the office? Is there something new that could be affecting the system during that hour (does the office power supply go on UPS?)?
Have you stayed at work and monitored the jobs one night to see if you can 'catch' anything that might cause the problem?
-SQLBill
March 25, 2003 at 12:49 pm
Couple more questions... have you tried running the jobs at any other time of the day?
Have you tried running each step individually?
Can you create some new jobs for a test? Copy each step and use it to create it's own job. Then disable the original job and schedule the individual jobs to run. That might help identify the problem.
-SQLBill
March 25, 2003 at 3:30 pm
are your jobs using xp_sendmail
if you are sending mail and the mail server or job fails you must reboot the server in order for the jobs to work again
March 26, 2003 at 2:27 am
most of schedules were done by scheduling the DTS package to start at a certain time.
I have tried refreshing the individual jobs but they still stay on the same message.
No other jobs have been added or edited.
I haven't tried the steps individually because they are almost all single step jobs. The Multiple step jobs will only run if I run the DTS packages myself. They do not run if I do "Start Job" on the job.
I have tried copying the jobs to make them work. They do work once copied and recheduled but they cease working soon after.
March 27, 2003 at 7:26 am
I'm very curious if anyone finds a solution to this. It's very timely as a DTS job that I have scheduled for a daily run appears to have frozen again last night. This probably has been happening about once a month or so.
I don't have access to this box as it is a production box serving several apps. What I have derived when working with the DBA who supports it are the same symptoms as Mad_Eddie, "Executing Step 1". I think the DBA has been able to kill the job though. Also, if he runs the DTS package directly it works fine.
The only solution, that's not 'fix', that has seemed to work is to restart SQL server. Actually, I think they had to bounce the box. My mind isn't working too well yet this morning. The issue here is that this can only be done off hours and often only on weekends which can leave me stranded for a few days.
Hope someone has seen and resolved this. Up until now I thought I was the only one having this issue.
March 27, 2003 at 8:03 am
I have seen process sqlmaint.exe hung during the execution of jobs that are created by maintenance plan and jobs seem to run endless and can't be stopped from SQL Agent manually. I have to go to task manager to stop them or restart SQL Server. You may check task manager too if same problems occur again.
March 27, 2003 at 4:15 pm
One possible reason for this is the server is too busy, Besides SQL, maybe some other applications on the server. If so, you'd better
move some applications or update your hareware. To stop the job, do not stop sql server services, just stop from task manager or directly stop sql agent.
Robert
March 28, 2003 at 3:38 am
UPDATE::
After scouring the internet lookiong for solutions to my problem I seem to have found....at least a solution which is working so far (/me touches wood)
The problem seems entirely based around SQL Mail.
Using QA I found I couldn't run xp_stopmail and couldn't run xp_sendmail because it was already running - pointing to a problem.
I then went to all my jobs and DTS packages and removed any E-mail reporting or SQL Mail tasks. Bingo - All the jobs stopped instantly and have been running and closing perfectly ever since...
hope this is some help
March 31, 2003 at 11:21 am
According to the DBA here there is supposed to be a patch for this problem.
So, how are you dealing with reporting after removing the e-mails? I depend upon these to send me the summary/exception file output for the evening's run.
March 31, 2003 at 1:05 pm
Mail server is the problem.
I had the jobs stucked last weekend too, but has different status as 'Performing Compltion Action'. They have been hanged in this stage for almost two days.
When I viewed job History, there is no failed records. I restarted servers and jobs status shows 'Not Running' with failed red sign. After next scheduled job runs, it completed and red sign is gone. Interesting is that there is no failed record when viewing history.
I just found out from our network team that our Exchange Server had been down for about six hours which is the period that my SQL Jobs ran.
Jie Ma
Jie Ma
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply