November 11, 2003 at 10:01 am
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
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
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
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
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
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