SQL Server 2005 Sleeping SPID Blocking Another SPID
I find a lot of sleeping processes in my SQL Server database and it looks like one of these pairing SPIDs is blocking another process and this process is paused as well ...
Can someone explain this ...
1.) How does a sleeping process block another process? 2.) I see a lot of sleeping processes ... is this normal?
thanks
a source to share
Locks are held for different durations, but the most common blocking locks - X locks - are held for the duration of the transaction. Since the lifetime of a transaction is completely unrelated to the lifetime of the packet, it is perfectly okay to have a sleeping SPID for its own locks, it simply means that the client has started a transaction and performed some updates. Once the client decides to continue and issues a command to the server to commit or rollback the transaction, there will be no blocking.
Another common lock is database session lock, which is a shared lock held by a connection using the database. The simple act of maintaining a connection will contain a lock, but usually this is only a conflict with operations that are trying to acquire the X lock on the database, such as ALTER DATABASE DDL.
There are more esoteric cases like locks with two commit phases after recovery, but these are probably not your problems. What you see is most likely one of the trivial cases of a user who launches something from SSMS and forgets to commit, or an application that contains long transactions might even leak.
a source to share
1.) How does a sleeping process block another process?
The sleeping process is waiting for work. Double check if the sleeping process is actually blocking something because it is really unlikely.
2.) I see a lot of sleeping processes ... is this normal?
Many sleeping processes are perfectly normal. For example, a pool of connections from one web server typically supports 10 processes. This is great for performance.
Here is a list of the process states :
Status Meaning
---------------------------------------------------------------------------------
Background The SPID is running a background task, such as deadlock detection.
Sleeping The SPID is not currently executing. This usually indicates that the
SPID is awaiting a command from the application.
Running The SPID is currently running on a scheduler.
Runnable The SPID is in the runnable queue of a scheduler and waiting to get
scheduler time.
Sos_scheduler_yield The SPID was running, but it has voluntarily yielded its
time slice on the scheduler to allow another SPID to acquire
scheduler time.
Suspended The SPID is waiting for an event, such as a lock or a latch.
Rollback The SPID is in rollback of a transaction.
Defwakeup Indicates that the SPID is waiting for a resource that is in the
process of being freed. The waitresource field should indicate the
resource in question.
a source to share