ansaurus

Question

Answer 1

+2 A:

KILL <spid>; where is the @@SPID of the spinning procedure. Use select * from sys.dm_exec_requests to identify the @@SPID (session_id) if you don't know it.

Update

Yeap, that is an infinite loop. You're missing a FETCH inside the WHILE loop:

fetch next from myCursor into @title, @body 
while @@fetch_status = 0 begin 
  EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = 'SqlAlerts', 
    @recipients = '[email protected]', 
    @body = 'The stored procedure finished successfully.', 
    @subject = 'Automated Success Message' ; 

   // Add this line:  
   fetch next from myCursor into @title, @body 
end

Remus Rusanu 2010-07-07 03:23:35

nice and simple +1

Michael Haren 2010-07-07 03:25:19

Hmm I am not sure which one it is. It was called csp_test but I can't see it. I only see one running and it told me I can't kill my own process.

chobo2 2010-07-07 03:31:16

Use this query: `select * from sys.dm_exec_requests r outer apply sys.dm_exec_sql_text(r.sql_handle) t`. This will also project the actual statement being executed. From what you posted there, it doesn't look like there is any looping procedure, btw.

Remus Rusanu 2010-07-07 03:38:03

I will post you what that resulted. It looks almost the same. Well I don't know. I ran that stored procedure now every second my gmail gets like 10 new messages with the exact same msg I told it to send. So I don't know how to stop it.

chobo2 2010-07-07 03:42:50

You don't have any looping procedure executing. There must be something else that is sending those mails. A trigger perhaps?

Remus Rusanu 2010-07-07 03:44:41

Don't know I just have the database mail account that I set up. I posted my stored procedure. I only setup database mail profile and that's it. I did not setup any triggers.

chobo2 2010-07-07 03:46:17

Try `KILL 55;`, but I suspect that is the SELECT from sys.dm_.. itself. You sure you execute it on the right server? Anyway, your procedure needs to FETCH inside the loop, see my update.

Remus Rusanu 2010-07-07 03:58:04

Ya I think my things needs alot of things but first I need to stop this. Ya I tired that but it won't let me kill it. I just don't know where to look anymore it just keeps sending emails and I don't know why. I deleted the profile, I deleted the datbase table I tried to do sp_stop_dbmail but nothing works.

chobo2 2010-07-07 04:02:09

The procedure has robably stopped. Righ now, all those mails send are in transit. Just wait untill it drains. How many could they be? 1M, 100M? Delete your inbox every now and then...

Remus Rusanu 2010-07-07 04:16:29

Well I just made a filter to send them right to the trash can. I would like to contact google and tell them about this but I can't find an email address to send to them. It seems like a huge flaw that I can send email after email and it's like they don't care. They make it next to impossible to find how to contact them and tell them these problems.

chobo2 2010-07-07 04:22:03

Hopefully it stops. From now on I am making a garbage account that I don't care. Knowing google they probably will try to ban me or something.

chobo2 2010-07-07 04:22:55

@chobo2 - they may eventually throttle you or blacklist you.

Cade Roux 2010-07-07 04:23:50

Answer 2

+1 A:

Find your rogue connection with the activity monitor and kill it. Or if it's an emergency, just recycle the SQL Server service.

Michael Haren 2010-07-07 03:23:47

or what @Remus said--that's much simpler

Michael Haren 2010-07-07 03:24:32

Answer 3

A:

I'd just cycle the SQL Server service, but this is what you need to do to fix your code before you test again:

open myCursor

fetch next from myCursor into @title, @body
while @@fetch_status = 0 begin

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'SqlAlerts',
        @recipients = '[email protected]',
        @body = 'The stored procedure finished successfully.',
        @subject = 'Automated Success Message' ;

    -- Need another fetch here - you are never attempting to seek to the next row
end

-- need CLOSE myCursor here
-- need DEALLOCATE myCursor here

Cade Roux 2010-07-07 03:52:03

Ya I know I forgot to grab that from the tutorial. How do I cycle the server?

chobo2 2010-07-07 03:53:31

@chobo2 Go to control panel - Administrative tools - Services - find the SQL Server Service - restart it.

Cade Roux 2010-07-07 04:00:50

Ok I restarted "SQL Server (SQLSERVER2005) service" but the emails keep coming.

chobo2 2010-07-07 04:04:25

ansaurus

tags:

views:

answers:

How to stop Databasemail from executing

related questions