Question:
In sql server 2005 when I execute sp_who2, I see lot of processes with
“Suspended” status. I did not see them in sql server 2000. What does
“Suspended” status exactly mean in sql server 2005?
Answer:
status
nchar(30)
Process status. The possible values are:
dormant. SQL Server is resetting the session.
running. The session is running one or more batches. When Multiple Active
Result Sets (MARS) is enabled, a session can run multiple batches. For more
information, see Using Multiple Active Result Sets (MARS).
background. The session is running a background task, such as deadlock
detection.
rollback. The session has a transaction rollback in process.
pending. The session is waiting for a worker thread to become available.
runnable. The session’s task is in the runnable queue of a scheduler while
waiting to get a time quantum.
spinloop. The session’s task is waiting for a spinlock to become free.
suspended. The session is waiting for an event, such as I/O, to complete.