SQL Server 2005 Database Mail Install Hangs

By:   |   Updated: 2007-06-08   |   Comments (3)   |   Related: 1 | 2 | 3 | More > Database Mail

Problem

In SQL Server 2005, Microsoft introduced Database Mail the replacement for SQL Mail. When setting up Database Mail a few options need to be set including enabling Database Mail, setting up profiles and also making sure that Service Broker is enabled. Most of these options can be configured on the fly by just selecting "Configure Database Mail", but when running the install and selecting "Yes" to activate Service Broker for "msdb" the installation just hangs and the install never completes.

Solution

Setting up Database Mail is pretty straight forward. Just right click on Database Mail in the Management folder and select "Configure Database Mail". Select "Next" and then select "Set up Database Mail by performing the following tasks:". After you select "Next" again you may be prompted with the following message.

To activate Service Broker in the "msdb" database just select "Yes". Once you select yes you may find that this process just hangs and never completes.

If you take a look at processes that are running either by using Activity Monitor or by running sp_who2 you will see there is a blocked process. In the screen shot below you can see that SPID 53 is being blocked by SPID 54. If you take a closer look you can see that SPID 53 is running in "master" and SPID 54 is running in "msdb".

If we use the fn_get_sql command we can find out exactly what is being run by these two processes. The following code allows us to get information about SPID 53, by setting the session_id = 53.

DECLARE @Handle varbinary(64);
SELECT @Handle = sql_handle
FROM sys.dm_exec_requests
WHERE session_id = 53 and request_id = 0;
SELECT * FROM ::fn_get_sql(@Handle);
GO

After running the above we can see that Database Mail install is trying to alter database "msdb" to enable Service Broker. Below is the output from the above query.

We can also do the same for SPID 54 to see what this process is doing.

DECLARE @Handle varbinary(64);
SELECT @Handle = sql_handle
FROM sys.dm_exec_requests
WHERE session_id = 54 and request_id = 0;
SELECT * FROM ::fn_get_sql(@Handle);
GO

After running the above, we can see there is no activity for SPID 54 as shown in the below output.

So based on this what is happening? It looks like the Database Mail setup is trying to do an ALTER DATABASE command. In most cases an ALTER DATABASE command can be run without any issue to other processes that are running. If you try to run the ALTER DATABASE command with other options such as changing the database recovery model the statement completes without issue, such as:

ALTER DATABASE msdb SET RECOVERY FULL

If we take a closer look at the above output from sp_who2 we can see that SQL Agent is the program that is currently running that is using the "msdb" database. This is the SPID (54) that is blocking SPID (53) from completing the ALTER DATABASE statement. Let's see what happens if we stop SQL Agent.

As soon as we stop SQL Agent the ALTER DATABASE command finishes and we move on to the next step in the Database Mail setup. So from this we can see that some of the ALTER DATABASE commands require exclusive access to the database in order for them to take affect and turning on Service Broker is one of these options.

To make sure everything is still working OK, we can start SQL Agent again and see that this starts without a problem and we are still able to continue on with the Database Mail setup.

Next Steps
  • If you have run into this issue setting up Database Mail, this may be the fix your are looking for. In most cases this will probably not pop up, but if it does you now know how to solve this issue.
  • As we have seen not all Alter Database options can be performed without exclusive database access, so keep this in mind when making database setting changes.
  • Follow these simple troubleshooting steps next time you have a blocking issue.


Last Updated: 2007-06-08


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, July 09, 2015 - 10:45:45 AM - Doug Back To Top

Nice! Ran into this exact situation after a system move. After resolving the SPID blockage, it went thru.


Wednesday, July 30, 2014 - 12:55:31 PM - Jeff Back To Top

Brilliant!  I was able to fix this long standing problem in less than a minute.  Thank you!

 

--Jeff


Thursday, June 23, 2011 - 2:42:54 PM - kfewer Back To Top

I am trying to set up Database Mail on a new SQL 2005 installation and it is asking for Service Broker to be enabled. I have have never been asked this before. When I look at what is different between this new server and all our other servers, the only difference I can see is that all the other servers have Notification Services installed and this one doesn't.

Is Service Broker part of Notification Services? I cannot find any documentation stating Notification Services is a requirement for Database Mail. Is enabling Service Broker and enabling a Service Broker 'endpoint' the same thing? I see our working DB Mail servers do not have an end point enabled yet setting up the DB mail never asked to enable Service Broker. This leads me to think they are not the same thing.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools