Unable to send email in SQL 2005

When I run test email in Database mail in SQL 2005 Management Studio, I got an error below
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
ADDITIONAL INFORMATION:

Service Broker message delivery is not enabled in this database. Use the ALTER DATABASE statement to enable Service Broker message delivery. (Microsoft SQL Server, Error: 14650)

Can anyone tell me what I can do to resolve this?
Thanks.
atlasdevAsked:
Who is Participating?
 
mcv22Connect With a Mentor Commented:
Try stopping the SQL Server Agent windows service and then trying to enable service broker in MSDB
0
 
atlasdevAuthor Commented:
After reading the article, I think the problem in my issue is that my MSDB database's service broker is not enabled. I am trying to enable the service broker on MSDB database. I used the query below

USE master ;
GO

ALTER DATABASE AdventureWorks SET ENABLE_BROKER ;
GO

However, it takes forever to the query to finish. I think something is wrong here. When I checked the process info, I see the query's process wait type is LCK_M_X.
Is there something else I need to do to enable the service broker?
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
mcv22Commented:
Try running:


ALTER DATABASE msdb SET ENABLE_BROKER ;

instead of doing it in the AdventureWorks database
0
 
atlasdevAuthor Commented:
Actually I am using ALTER DATABASE msdb SET ENABLE_BROKER. I forgot to change it in my previous post.
0
 
mcv22Commented:
Its likely some other session is blocking your attempt to enable the service broker. Run the following in a new query window:

select blocking_session_id from sys.dm_exec_requests where session_id = SPID

where SPID is the sessionID of the query window trying to enable service broker.
0
 
atlasdevAuthor Commented:
I ran your query and see that SQLAgent-Alert Engine is the process blocking my query process.
0
 
atlasdevAuthor Commented:
I tried to delete that process of SQLAgent-Alert Engine but it keeps coming back. I can restart the query again but the SQLAgent -Alert Engine keeps coming back at the same time, too.
0
 
atlasdevAuthor Commented:
Thank you very much. The testing email is working now.
0
All Courses

From novice to tech pro — start learning today.