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?

Improve company productivity with a Business Account.Sign Up

x
 
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.