How It Works: What is a Sleeping / Awaiting Command Session


This issue is as old as SQL Server.  In fact, it goes back to Sybase days but continues to fool and puzzle administrators.

A session with that status of sleeping / awaiting command is simply a client connection with no active query to the SQL Server. The table below shows the transitions from running to sleeping states for a session.

Connect Running
Connect Completed Sleeping / Awaiting Command
select @@VERSION Running
select completed Sleeping / Awaiting Command

The question usually arises around a session that is holding locks and its state is sleeping / awaiting command.  If the client has an open transaction and the client did not submit a commit or rollback command the state is sleeping / awaiting command.    I see this quite often with a procedure that times out.  

 

Create proc myProc

As

                Begin tran

                Update authors ….

                Waitfor delay ’10:00:00’   — time out will occur here  (simulates long workload)

                rollback

go

 

When run from the client with a 30 second query timeout the transaction will remain open because the client indicated it wanted to ‘cancel execution’ and do no further processing.   To get automatic rollback in this situation transaction abort must be enabled.  You now have an open transaction with a SPID sleeping/awaiting command.

The situation can be caused by many other variations but it is always a situation where the SQL Server is waiting for the next command from the client.   Outside a physical connection problem these are always application design issues.

Bob Dorr
SQL Server Senior Escalation Engineer

Comments (5)

  1. David says:

    OK, so do I do anything to clear the connections now to get users working before I take it up with the design company?

  2. * Please enter a comment says:

    * Please enter a comment  * Please enter a comment  * Please enter a comment  * Please enter a comment  * Please enter a comment  * Please enter a comment

  3. Sharon says:

    Is this sleeping/awaiting command a reason for the increasing size of tempdb?

  4. Simon Davies says:

    1. Start SSMS, select master database, new query, execute sp_who2
    2. All result rows indicate DBName as master or NULL
    3. Now select any non-system database in object explorer, select any table, right-click and ‘Edit Top 200 Rows’
    4. Execute sp_who2 again in master query window
    5. Now there are 2 result rows referencing the selected database from step 3, with program name SSMS
    6. Delete the ‘Edit Top 200 Rows’ pane/window
    7. Execute sp_who2 again in master query window
    8. There is still 1 result row referencing the selected database from step 3, with program name SSMS

    Why does that connection not disappear with the pane/window that initiated it?

Skip to main content