Issue with configuring DB mail in SQL 2k5

• Ammad Baig

  Mr or Mrs. 500

  Points: 577

  More actions

  August 8, 2007 at 11:28 pm

  #125268

  Hi,

  I am trying to setup DB mail in our Sql 2k5 environment. There are a few requirements such as

  1. Enabling service broker

  2. Create the mail profile

  When I go to Management | Database mail, right click and choose 'Configure DB mail'. I choose 'Setup DB Mail by performing the following tasks', Click Next. I get a msg stating that 'DB mail svc depends upon service broker. SB is not active in msdb....." When I choose 'Yes' to enable the SB it just hangs there and nothing happens...

  I run the following statement to check the status of SB, and it's set to '0'.

  SELECT

  is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;

  I then tried to run the following statement in Query Analyzer:

  USE

  master ;

  GO

  ALTER

  DATABASE msdb SET ENABLE_BROKER ;

  GO

  The command just never finishes...It just stays there with a status of 'Executing'.

  Has anyone ever experienced this issue? If so please enlighten me as I am totally at a standstill.

  Thanks!

• MarkusB

  SSC-Dedicated

  Points: 37370

  More actions

  August 9, 2007 at 4:43 am

  #725663

  Ammad,

  you probably need to stop the SQL Agent before executing the ALTER DATABASE command. The command needs exclusive access to the database and is just waiting until thats possible.

  In fact no other user connections to the database in question must be open. In the msdb, the most likely culprit is SQL Agent. 

  Markus

  [font="Verdana"]Markus Bohse[/font]

• Ammad Baig

  Mr or Mrs. 500

  Points: 577

  More actions

  August 9, 2007 at 9:42 pm

  #725889

  Thanks Markus...that worked...I also found in BOL "Database Mail Configuration Wizard"

  Note:
  Enabling SQL Server Service Broker in any database requires a database lock. If Service Broker was deactivated in msdb, to enable Database Mail, first stop SQL Server Agent so Service Broker can obtain the necessary lock.