This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(70.4, 13%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
Hi All,
Today came across a blocking issue from the application. A very simple INSERT ran and the spid is in "sleeping" "AWAITING_COMMAND" with an open transaction and lock on the table. After 15 hours, then comes a spid which "truncate that table" and it is waiting on Sch-M lock. There is no implicit_Tran on. but don't know why it has one open transaction. I seeing this behavior quite often and eventually had to kill that insert spid. isolation is Read- committed and RCSI is on for that particular db.
<additional_info>
<text_size>-1</text_size>
<language>us_english</language>
<date_format>mdy</date_format>
<date_first>7</date_first>
<quoted_identifier>ON</quoted_identifier>
<arithabort>OFF</arithabort>
<ansi_null_dflt_on>ON</ansi_null_dflt_on>
<ansi_defaults>OFF</ansi_defaults>
<ansi_warnings>ON</ansi_warnings>
<ansi_padding>ON</ansi_padding>
<ansi_nulls>ON</ansi_nulls>
<concat_null_yields_null>ON</concat_null_yields_null>
<transaction_isolation_level>ReadCommitted</transaction_isolation_level>
<lock_timeout>-1</lock_timeout>
<deadlock_priority>0</deadlock_priority>
<row_count>1</row_count>
<command_type>AWAITING COMMAND</command_type>
<sql_handle>0x01000b002c203603d0be7be26602000000000000</sql_handle>
<statement_start_offset>-1</statement_start_offset>
<statement_end_offset>-1</statement_end_offset>
<host_process_id>25578</host_process_id>
<group_id>2</group_id>
<original_login_name>app_ors</original_login_name>
</additional_info>
My questions, by default any connection to sql server will be auto-commit mode , (correct me if I am wrong). I have tested this with a small insert on dept table and as and when the insert is done, its auto-commited by default and there is no open_tran. however, it is "sleeping" and "awaiting_command" as expected. I opened a new ssms window and tried to SELECT * FROM DEPT; I was able to see the new record which means the insert has been auto committed.
In case, of the application WHY it is keeping the transaction open in that spid for a simple insert.
Am I missing anything? what else can we collect or check to know the root cause of that open txn? is there a possibility of another spid has open that txn( i dont think so) !! . please give some ideas.
Regards,
Sam
In case, of the application WHY it is keeping the transaction open in that spid for a simple insert.
I guess the application starts an explicit transaction, and as long as it don't commit, it's quite normal to see the process in SLEEPING state with an open transaction.
is there way to tell an explicit transaction has been started by the session? we see so much blocking because of such sessions?
Found this query very useful to tell if it explicit or implicit transaction.
select
s.spid,
s.open_tran,
s.login_time,
s.last_batch,
at.transaction_begin_time,
t.transaction_id as txnid,
t.is_user_transaction,
at.name as txn_name, ---implicit transaction or "user_transaction"
at.transaction_type,
CASE at.transaction_type
WHEN 1 THEN 'Read/Write'
WHEN 2 THEN 'Read-Only'
WHEN 3 THEN 'System'
WHEN 4 THEN 'Distributed'
ELSE 'WHAT HAVE YOU DONE'
END AS transaction_type1,
at.transaction_state,
CASE at.transaction_state
WHEN 0 THEN 'Uninitialized'
WHEN 1 THEN 'Not Yet Started'
WHEN 2 THEN 'Active'
WHEN 3 THEN 'Ended (Read-Only)'
WHEN 4 THEN 'Committing'
WHEN 5 THEN 'Prepared'
WHEN 6 THEN 'Committed'
WHEN 7 THEN 'Rolling Back'
WHEN 8 THEN 'Rolled Back'
ELSE 'WHAT HAVE YOU DONE'
END AS transaction_state2,
s.[status], --sleeping
s.cmd, --"AWAITING COMMAND"
CASE ss.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS transaction_isolation_level,
st.[text] as qry_txt
from sys.sysprocesses s
left outer join sys.dm_exec_sessions ss on s.spid = ss.session_id
left outer join sys.dm_tran_session_transactions t on s.spid =t.session_id
left outer join sys.dm_tran_active_transactions at on t.transaction_id = at.transaction_id
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS st
where s.spid =54
One question application team/dev team had is, is there an option available to automatically commit txn's which are opened by the application when using Explicit txn? is there any option like that? I didn't know how to answer that, don't know if that is possible and if they are sure of what they are talking about. Want to know your inputs on this.. any analogy can be used to make them understand that , if they open the txn, they have to close it explicitly??
At that time, all i could think of and suggested if they want that kind of behavior , they need use IMPLICIT TRANSACTION OFF.
Please correct me if I am wrong.
is there an option available to automatically commit txn's which are opened by the application when using Explicit txn?
When an app starts explicit a transaction, then it also have to explicit commit the transaction. Everything else is an implicit transaction.
Note: If an app starts an explicit transaction and shutdown unexpected, a transaction rollback occurs.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(307.2, 78%, 39%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EZB%3C/text%3E%3C/svg%3E)
Hi,
Found related stuff somewhere, may help:
In SQL Server, a session with the status of “sleeping” or “awaiting command” represents a client connection that is currently idle and not actively executing any queries against the database. Let’s delve into the details:
Sleeping / Awaiting Command State:
When a session is in this state, it means that the client connection exists, but there is no active query being processed by the SQL Server.
The transition from running to sleeping states occurs as follows:
**Connect Running**: The session is connected and actively executing a query.
**Connect Completed**: The query execution completes, and the session transitions to a waiting state.
**Sleeping / Awaiting Command**: The session is idle, waiting for the next command from the client.
Common scenarios for this state include:
A client connection that has not submitted any queries yet.
A session holding locks due to an open transaction where the client did not issue a commit or rollback command.
Situations where a procedure times out, leaving an open transaction.
To automatically roll back transactions in such cases, ensure that transaction abort is enabled.
**Application Design Considerations**:
The “sleeping / awaiting command” state is typically an application design issue.
Outside of physical connection problems, it arises due to how the application interacts with the database.
For example, if a client cancels execution without explicitly committing or rolling back a transaction, the session remains in this state.
Always review your application code and ensure proper handling of transactions and query timeouts.
```Remember, while “awaiting command” sessions may seem puzzling, they are part of the normal behavior of SQL Server and often reflect application design choices.