Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Can't Kill Job

Posted on 2007-07-30
7
Medium Priority
?
579 Views
Last Modified: 2008-01-09
SQL 2000 SP4 (8.0.2039) + Windows 2003 Enterprise x64 SP2 :

SQL Agent job hangs and I can't kill it with right-click 'stop job.'  Is there any other way to kill the job without stopping the whole SQL service?  I looked through individual SQL processes under Current Activity, and think I found the right one, but there is no column to identify the job - plus when I right-clicked and selected "kill job," it didn't work anyway.

I'm guessing that there is no way to kill this job through Windows, because all the jobs are running under the same sql process?
0
Comment
Question by:scottedwards2000
  • 4
  • 3
7 Comments
 
LVL 14

Accepted Solution

by:
twoboats earned 2000 total points
ID: 19595321
"I'm guessing that there is no way to kill this job through Windows, because all the jobs are running under the same sql process?"

True - they're all threads of SQL.

If

kill spid

doesn't do it, then a sql bounce is your only option
0
 

Author Comment

by:scottedwards2000
ID: 19595386
That's a bummer... I'm guessing then, that there is no way to identify the thread with Process Explorer and kill it?  :)

Hate to open up a can of worms, but after sql 2005 came out, I've had rebuttals for almost every single "Oracle is better than SQL" argument (e.g. "readers don't block writers", etc.), but this strikes me as a pretty big weakness for an "Enterprise class" db.  What if I had 10,000 users on - so, I'd have to bounce them all just to stop some little job?
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19595455
I guess the argument would go something like "why would you need to kill a job that you've properly tested?", but it's not a perfect world.

Do you know why it hangs?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:scottedwards2000
ID: 19595608
I suspect that a log file is locked that I output an ftp command to.  I notice in Process Explorer that under sqlservr.exe, there are 2 processes:

1. cmd.exe
2. ftp.exe

So, I'm guessing when I output ftp to that file (using xp_cmdshell), it finds that it is locked and the job hangs.  I really need to rewrite this stored procedure with something more stable like wget.

Maybe if I killed those two processes under sqlservr.exe, it would unlock the job?  I don't want to risk screwing up any other sql jobs running though.
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19595681
It might unlock the job - though you might get an access denied, if the processes belong to SQL

0
 

Author Comment

by:scottedwards2000
ID: 19595794
Yep, you were right - got "access denied."  Oh, well, I guess I'm going to blame it on my use of xp_cmdshell - probably not generally a good idea to invoke shell from sql.  (at least that will make me feel better when I hear my Oracle friends rib me about this...)

Thanks, twoboats.
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19595810
Welcome

xp_cmdshell - I use it lots. Haven't had any problems really. Guess it depends what you're doing.

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question