We had an issue in production (2008R2 SP3) yesterday, where a developer was running a piece of code in SSMS that was executing sp_send_dbmail inside a transaction, and including an attachment from a network share on another server.

The developer reported the code "seemed to take longer than expected" and tried to stop the query. When the query failed to stop, he asked me to kill his query.

Using sp_WhoIsActive, I gathered the following info on the session:

  • PREEMPTIVE_OS_GETPROCADDRESS wait that was continuing to go up
  • Extremely low CPU, memory, IO usage
    • almost all time spent on the wait
  • had 1 open transaction
  • Held a schema modification lock, which was causing significant blocking
  • Current statement was executing xp_sysmail_attachment_load
    • Call stack is sp_send_dbmail --> sp_GetAttachmentData --> xp_sysmail_attachment_load

Due to the PREEMPTIVE_OS wait, the session cannot be killed from SQL Server until the OS yields/returns. Using TCPView, I confirmed there were no active network connections from the SQL Server to the file server with the attachment. I do not know if there was a connection that had since disconnected, or if it never made the connection.

Based on the data, my assumption is that there was some issue with accessing the attachment, which caused the session to hang, and the OS process to never yield/return.

Since this was impacting production by causing blocking, we ultimately failed over the FCI to another node, so that the instance restart would force the session to die.

My questions are:

  • Is there some way to reliably identify what OS process SQL was waiting on? (and thus try to "help" that process return--killing it etc)
  • Coming from the other direction: Is there a way to force a session to stop waiting for the OS with a PREEMPTIVE_OS wait so that we can kill it without restarting SQL?
  • Is there some factor in this scenario that make the session hanging on xp_sysmail_attachment_load more likely? (I'm working on a repro case, but can't reliably reproduce this yet. )
  • Are there other places I can/should look for additional information if this issue happens again?


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.