By: Greg Robidoux | Comments (3) | Related: 1 | 2 | 3 | > 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.
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
