Post reply

SQL Agent Process in Runnable even after KILLING

  • November 11, 2003 at 10:01 am

    #65732

    On one of my SQL Servers (SQL2K SP2, WIN2K SP2), I killed one of the process which was executing the following code. But even after 2 days it is still in KILLED/ROLLBACK state. Can you anyone let me know whats happening here.

    <pre id=code><font face=courier size=2 id=code>

    declare @rc int

    exec @rc = master.dbo.xp_smtp_sendmail

    @FROM = N'service.account@domain.com',

    @FROM_NAME = N'SQL Service Notices',

    @TO = N'first.person@domain.com;second.person@domain.com;third.person@domain.com;fourth.person@domain.com;fifth.person@domain.com',

    @cc = N'',

    @priority = N'NORMAL',

    @subject = N'XXXXXXXXXXXXX @ XX:XXAM Today Succeeded on XXXXXXXX Server',

    @message = N'XXXXXXXXXXXXX @ XX:XXAM Today Succeeded on XXXXXXXX Server',

    @type = N'text/text',

    @server = N'smtpmail.domain.com'

    select RC = @rc

    go

    </font id=code></pre id=code>

    .

  • November 11, 2003 at 10:03 am

    #481605

    Yesterday I tried killing the job from EM, and it went into CANCELLED status & not running now. But this process is still running as "runnable process" status (when run sp_who2).

    .

  • November 12, 2003 at 5:13 pm

    #481790

    Can't say I have a complete answer here, but we experience similar occurences. The cause in each case is with SQL mail and its inability to recover from a lack of response from the mail server it is communicating with (for whatever reason) In basic terms SQL agent has communicated to a mail server and is awaiting confirmation of completion. Our solution is to stop and restart the agent service (check on restart that no errors in the agent log re mail). If anyone has a more elegant solution please let me know.

    Derek

  • November 13, 2003 at 12:01 pm

    #481940

    Let me add to this, that if all else failed restarting the server itself is always helpfull and 99% of the time works.

    mom

  • November 14, 2003 at 8:47 am

    #482078

    dharper,

    Restarting the SQL Server Agent didn't work. Restarting the BOX itself did help earlier. But since we did the Server Upgrade recently, I didn't want to have this reboot during the business week. Because this box is being used 24 hrs from diff countries.

    mom: Yes. Rebooting the box did help in earlier scenarios. But I just wanted to check if there is any other alternative.

    .

  • November 14, 2003 at 10:05 am

    #482089

    Have a look at Q137983


    * Noel

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply