Schedule task not stopping

[Mike Byrd]

I've been running SQL Server for 5 years now and have not seen the following.

We have a scheduled night run task where we do various maintenance functions as well as print out the issued insurance policies for the day. All indications are that the run completed except that I did not get an email notification of success or failure.

When I went to enterprise manager, under jobs the night run seems to be stuck in the mode of "Performing completion actions". I cannot get it our of this mode back to "Not running". Trying to stop the task has no effect.

Any ideas/suggestions??

Thanks in advance.

Cheers,
Mike Byrd
Information Systems Manager
Texas Windstorm Insurance Association
Austin, Texas

[Gurukiran]

Mike, try this :

Using sp_who2, look up the spid of the still running job and see if it reports any changes in I/O for a few runs of sp_who2. If it still shows as a running process, but no change in I/O, it is a dead process. If it is running under 'sa' account, you will have to stop and restart the SQL Server service. Otherwise, you can try killing the process.

Hope that helps.

------------
Mike Byrd at 1/16/01 10:10:31 AM


I've been running SQL Server for 5 years now and have not seen the following.

We have a scheduled night run task where we do various maintenance functions as well as print out the issued insurance policies for the day. All indications are that the run completed except that I did not get an email notification of success or failure.

When I went to enterprise manager, under jobs the night run seems to be stuck in the mode of "Performing completion actions". I cannot get it our of this mode back to "Not running". Trying to stop the task has no effect.

Any ideas/suggestions??

Thanks in advance.

Cheers,
Mike Byrd
Information Systems Manager
Texas Windstorm Insurance Association
Austin, Texas

[Alain Gagne]

Mike,

Using sp_who2 some times to see if there is IO activities is the right way to check if the process is dead or not. We had this kind of problems with SQL Server 7 running on NT4. We called these : "the never ending DTS packages". They ran without any problem for a long time and suddenly we found out that they were happening around each months. We stoppeed them manually and then re-ran them and they were still hanging. The only way to prevent this was to reboot the server once a week. When we migrated to WIN2k, the problem vanished by itself and never occured since.

I would think about this :
Do you run maintenance tasks? Perhaps the database has degraded so much that it is hurting the performance.
Is the maintenance tasks are running at the same time? Perhaps there is a "lock" occuring.
Is there any other activity on the server that can "lock" the task?

You do not give a lot of details. If it does not help, give more details please.

Alain Gagne, Lead DBA
gagnea@msagroup.com


------------
Mike Byrd at 1/16/01 10:10:31 AM


I've been running SQL Server for 5 years now and have not seen the following.

We have a scheduled night run task where we do various maintenance functions as well as print out the issued insurance policies for the day. All indications are that the run completed except that I did not get an email notification of success or failure.

When I went to enterprise manager, under jobs the night run seems to be stuck in the mode of "Performing completion actions". I cannot get it our of this mode back to "Not running". Trying to stop the task has no effect.

Any ideas/suggestions??

Thanks in advance.

Cheers,
Mike Byrd
Information Systems Manager
Texas Windstorm Insurance Association
Austin, Texas

[Tom Cuscino]

Mike,

I had seen this happen since Ver. 6.5. It occurs for me consistently when the mail server is down at the time that SQLAgent is trying to send the email msg. The procedure runs but then, when the msg is not sent, the procedure hangs and will not run again until the SQLAgent service (or SQL Executive Service in older versions) is stopped and started. The good news is that you do not have to reboot the server or even down the database (MSSQLServer service), simply the SQL Agent!! Why hasn't this been fixed for so many versions???


------------
Mike Byrd at 1/16/01 10:10:31 AM


I've been running SQL Server for 5 years now and have not seen the following.

We have a scheduled night run task where we do various maintenance functions as well as print out the issued insurance policies for the day. All indications are that the run completed except that I did not get an email notification of success or failure.

When I went to enterprise manager, under jobs the night run seems to be stuck in the mode of "Performing completion actions". I cannot get it our of this mode back to "Not running". Trying to stop the task has no effect.

Any ideas/suggestions??

Thanks in advance.

Cheers,
Mike Byrd
Information Systems Manager
Texas Windstorm Insurance Association
Austin, Texas

[Darwin Hatheway]

You might bring up the task manager on the afflicted server and see if there's a process available to kill.

We have had similar problems with SQL Exec processes that run VB executables. In some cases, the .EXE was built without a default error handler, the job ran into some peculiar condition we hadn't planned for and it hung up. We decided that in these cases what was probably happening was that the VB runtime system had popped (or tried to pop up) a messagebox for us to respond to. Since the thing was running detached, the messagebox had no place to display and the job was stuck.

We&#39;d bring up the NT Task Manager, find the offending <program>.EXE in the list and kill it.

And then we&#39;d rebuild the executable with a better error handler.

It&#39;s just a thought, my experience with V7 is limited (most of our servers are still 6.5), and you want to be careful about what you kill.

regards,
Darwin



------------
Mike Byrd at 1/16/01 10:10:31 AM


I&#39;ve been running SQL Server for 5 years now and have not seen the following.

We have a scheduled night run task where we do various maintenance functions as well as print out the issued insurance policies for the day.
...

Any ideas/suggestions??

Thanks in advance.

Cheers,
Mike Byrd
...

[Tarun]

In 6.5 I faced exact problem you mentioned a few times. This problem was due to the mail server and used to get resolved by re-starting the mail server.
Hope this helps you.
Good Luck
Tarun


------------
Mike Byrd at 1/16/01 10:10:31 AM


I&#39;ve been running SQL Server for 5 years now and have not seen the following.

We have a scheduled night run task where we do various maintenance functions as well as print out the issued insurance policies for the day. All indications are that the run completed except that I did not get an email notification of success or failure.

When I went to enterprise manager, under jobs the night run seems to be stuck in the mode of &#34;Performing completion actions&#34;. I cannot get it our of this mode back to &#34;Not running&#34;. Trying to stop the task has no effect.

Any ideas/suggestions??

Thanks in advance.

Cheers,
Mike Byrd
Information Systems Manager
Texas Windstorm Insurance Association
Austin, Texas

[Sable]

This is caused by the mail server (ie: the Exchange Server) or the SQLMail exec? I also have this problem on one of my servers (7.0 on NT) and I&#39;m trying to get it resolved.

Thanks for your response.

------------
Tarun at 2/27/01 9:32:18 AM

In 6.5 I faced exact problem you mentioned a few times. This problem was due to the mail server and used to get resolved by re-starting the mail server.
Hope this helps you.
Good Luck
Tarun


------------
Mike Byrd at 1/16/01 10:10:31 AM


I&#39;ve been running SQL Server for 5 years now and have not seen the following.

We have a scheduled night run task where we do various maintenance functions as well as print out the issued insurance policies for the day. All indications are that the run completed except that I did not get an email notification of success or failure.

When I went to enterprise manager, under jobs the night run seems to be stuck in the mode of &#34;Performing completion actions&#34;. I cannot get it our of this mode back to &#34;Not running&#34;. Trying to stop the task has no effect.

Any ideas/suggestions??

Thanks in advance.

Cheers,
Mike Byrd
Information Systems Manager
Texas Windstorm Insurance Association
Austin, Texas