Tony Rogerson's ramblings on SQL Server

So you are using the @query parameter of sp_send_dbmail and all of a sudden you start getting blocks and your connection running sp_send_mail is the cause, your connection running sp_send_mail just sits there in a runnable state with the lastwaittype of MSQL_XP.

What is happening?

create table ##ed_volval ( acol int not null )

begin tran

insert ##ed_volval values( 1 )

    EXEC msdb.dbo.sp_send_dbmail

        @recipients='tonyrogerson@torver.net',

        @subject = N'Bad Idea',

        @body = N'Bad idea',

            @query = N'select * from ##ed_volval',

        @body_format = 'HTML',

        @profile_name = 'TORVER' ;

The above connection starts a transaction then inserts rows into a table; you then call sp_send_dbmail to fire off an email message.

Sp_send_dbmail actually spawns another completely separate connection into SQL Server using an extended stored procedure, because your connection has the table locked the spawned connection blocks which in turn prevents the connection that issued the sp_send_dbmail from continuing – stale mate!

To identify the spawned process you can track it through, on a real system it can be a tad problematic.

select program_name, spid, blocked, lastwaittype, waitresource

from master..sysprocesses

where blocked > 0

   or spid in ( select blocked from master..sysprocesses where blocked > 0 )

You will see two runs...

One will be the connection running sp_send_dbmail, this will have the lastwaittype of MSQL_XP; the second row will have a program_name of SQLCMD and will be blocked by the connection running sp_send_dbmail.

This should never happen if sp_send_dbmail used Service Broker properly; why it can’t just plonk the row onto the queue and let activation stored procedures pick up the task I don’t understand. It would allow the caller process to continue and the activation stored procedure would just get blocked until the transaction released.

So, if you ever get the lastwaittype MSQL_XP then look for any connections with a program_name SQLCMD and trace it through to see if it’s a sp_send_dbmail problem and if you can guarentee then use KILL on it to release the process running sp_send_dbmail.

Note – the pain here is that the connection running sp_send_dbmail (the blocker) cannot be killed!