CODEWITHSUNDEEP
sqlsql-serverblocking
user173552
user173552

Reputation: 1229

SQL Server 2005 Sleeping SPID blocking another SPID

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

Upvotes: 3

Views: 7039

Answers (3)

Larry McPhillips
Larry McPhillips

Reputation: 31

Is the sleeping process waiting for some other event, such as a web request? We had this problem today with a web app which was leaving a transaction open after finishing a request. Then it just sat there waiting for the next request while it had the database locked, due to the open transaction.

We have now fixed the issue, and here are two things I can advise you to check based on mistakes that we had made:

(1) Make sure that all of your BEGIN TRANSACTION statements have a corresponding COMMIT TRANSACTION at the end. Otherwise your app might incorrectly hold a transaction open while waiting for the next user request.

(2) Check all uses of THROW to see whether any THROW statements are inside of a transaction. If you want to throw an error during a transaction, you first have to ROLLBACK TRANSACTION before the THROW, or else SQL Server will leave the transaction open and the database locked.

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294387

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.

Upvotes: 6

Andomar
Andomar

Reputation: 238176

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.

Upvotes: 4

Related Questions