SQL Server 2005 Sleeping SPID blocking another SPID...

Question

I find many sleeping process my SQL Server database and looks like one of those sleeping SPIDs is blocking another process, and that process is getting suspended too...

Could some one please explain this...

1.) How can a sleeping process block another process? 2.) I see many sleeping process...is this normal?

Thanks

Answer 1

1.) How can a sleeping process block another process?

A sleeping process is waiting for work. Double check if there's really a sleeping process blocking something, because that's really unlikely.

2.) I see many sleeping process...is this normal?

Many sleeping processes is perfectly normal. For example, the connection pool from a single web server usually keeps 10 processes open. This is great for performance.

Here is a list of 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.

Answer 2

Locks are held for various durations, but the most common blocking locks, the X locks, are held for the duration of the transaction. Since transaction lifetime is completely unrelated to batch lifetime, it is absolutely normal to have a sleeping SPID to own locks, it simply means the client has started a transaction and executed some updates. As soon as the client decides to continue and issues a command to the server to commit or rollback the transaction, the blocking will be gone.

Other frequent locking is database session lock, which is a shared lock held by a connection using a database. The simple act of maintaining the connection will hold the lock, but usually this only conflict with operations that try to acquire an X lock on the database, like ALTER DATABASE DDL.

There are more esoteric cases, like two-phase-commit locks held after recovery, but those are probably not your problems. What you're seeing is most likely one of the trivial cases of an user that runs something from SSMS and forgets to commit, or an application that holds long transactions, perhaps is even leaking them.