Unable to send email in SQL 2005

http://msdn.microsoft.com/en-us/library/ms189959(v=SQL.90).aspx

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?

Try running:


ALTER DATABASE msdb SET ENABLE_BROKER ;

instead of doing it in the AdventureWorks database

Actually I am using ALTER DATABASE msdb SET ENABLE_BROKER. I forgot to change it in my previous post.

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.

I ran your query and see that SQLAgent-Alert Engine is the process blocking my query process.

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.

Thank you very much. The testing email is working now.