INF: SQLMail Concurrency and the Kill CommandID: Q164998
|
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.
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.
Additional query words: block
Keywords : kbusage SSrvGen SSrvLock
Version : 4.21 4.21a 6.0 6.5
Platform : WINDOWS
Issue type : kbinfo Last Reviewed: April 9, 1999