INF: SQLMail Concurrency and the Kill Command

• Microsoft SQL Server, versions 4.2x, 6.0, and 6.5

SUMMARY

The SQLMail extended stored procedures are synchronized and protected from re-entry, which may cause blocking. Additionally, the KILL command is not always able to remove a process that has used SQLMail.

MORE INFORMATION

Because SQLMail is synchronized, it is difficult to determine blocking with it. For example, if two clients use xp_sendmail at the same time, one of the clients runs, and the other waits for the first to complete. If a query is run on sysprocesses, the second client will show a status of runnable, a command of Execute, a blocked value of 0, and (on SQL Server versions 6.0 or 6.5) a waittype of 0x0000. Once the first client has completed, the second is no longer blocked, and will then be able to run.

You can use the 4032 trace flag, SQL Trace, or DBCC INPUTBUFFER to check the command that a process is running to help diagnose this situation.

Another limitation of SQLMail is that if xp_sendmail is called with the optional @query parameter, SQLMail makes a loopback connection that blocks the KILL command. For example, suppose a client with process id (spid) 10 runs xp_sendmail with a long running query, which is on spid 11. If the Kill command is used on spid 10, the SQL Server errorlog reports that the spid was killed. However, the spid keeps running because it is waiting on the results of spid 11. In this situation, the Kill command must also be run on spid 11 to terminate the client. Unfortunately, there is no way to determine the loopback spid used by SQLMail.