SQL Server Performance

SQL Server 2005 DB Mail not working - can't delete mail queues - 'NOTIFIED'

Discussion in 'ALL SQL SERVER QUESTIONS' started by Trev256b, Nov 27, 2011.

  1. Trev256b Member

    Hi - I can't get db mail working or delete the db mail queues. I've restarted sql server and sql server agent service. db mail stored procedures is enabled in surface area configuration. service broker in msdb is enabled. i've tried to delete the mail queue by running the following:

    DECLARE @GETDATE

    datetime
    SET

    @GETDATE =GETDATE

    ()

    EXECUTE

    msdb.dbo.sysmail_delete_mailitems_sp  @sent_before = @GETDATE

    ;

    GO

    But when I run the following there are still a number of mails listed with STATE as 'NOTIFIED'.

    EXEC msdb.dbo.sysmail_help_queue_sp @queue_type ='mail'

    These can't be removed. Any idea on how to get rid of these and clear the database mail queue. The enviornment is SQL Server 2005 SP3 on Windows 2003 SP2. I think if I delete the queues then the emails may be able to be sent.
    
    I've also tried to stop and restart the db mail by running the following two lines but get this error:
    The activated proc [dbo].[sp_sysmail_activate] running onqueue msdb.dbo.ExternalMailQueue output the following:  'Error executing extended stored procedure: Invalid Parameter'

    

    Code run to stop/start db mails is:

    sysmail_stop_sp

    --stopping the queue using

    sysmail_start_sp

    -- starting the queue using

    Any idea on how to resolve this?
  2. johnson_ef Member

  3. Trev256b Member

    There are no results - 0 rows in sys.conversation_endpoints - so this is not applicable - any other ideas?
  4. Trev256b Member

    Can anyone help here? Is this posted in the correct forum?
  5. Luis Martin Moderator

    The forum is correct. About help, you wait for others members.
  6. RamJaddu Member

  7. Trev256b Member

    no stopped working after initally working for some time. if nobody has good suggestion for fix - what is the easiest way to re-install to fix? is there a minimal install to fix?
  8. satya Moderator

    There could be some issues related to SMTP, my guess.
    However if you happen to see it works initially and then not, better to go through operating system, network and other layers to fix the issue.

    We cannot give you step by step suggestions on the problems that you are getting, the voluntary help you get from forums is enormous and it will be more useful if you can take your time to investigate.
  9. Trev256b Member

    ok - thanks satya - looks like i will have to go for re-install of db mail, or sql server!! thanks!

Share This Page