How to find not-committed transaction in SQL Server 2008 exactly?

[Peng Liu]

My C++ code accesses SQL Server by ODBC driver 2.x, and calls
SQLSetConnectAttr( pMSSQLHandles->hDbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0 );
to set the transaction to implicit transaction. I also call SQLEndTran() to commit the transaction.

After my application starts， in SQL Server 2008 Managment Studio, run "DBCC OPENTRAN", no active open transaction is reported. And "select @@TRANCOUNT" return 0.

However, if run
select * from sys.dm_tran_session_transactions
we can see there is one record， and we can also find the transaction corresponding to this record in sys.dm_tran_active_transactions.

Below SQL script will return 1 records.
select s.dbid, s.spid,s.loginame, s.status,d.name,s.last_batch,datediff(minute,s.last_batch,GETDATE()) as IdleTimeInMin,
s.open_tran,t.text
from sys.sysprocesses s
join sys.databases d on d.database_id = s.dbid
cross apply sys.dm_exec_sql_text (s.sql_handle) t
where d.name = 'XXX' and s.loginame <> 'sa'
order by s.last_batch

I am curious that which method is the exact one to get all the not-committed transactions.
What is the meaning of the records in dm_tran_active_transactions, dm_tran_session_transactions and dm_tran_database_transactions?
What is difference between Open transaction and active transaction?

Thanks in advance.

[Erland Sommarskog]

Peng Liu (liupe...@gmail.com) writes:
> My C++ code accesses SQL Server by ODBC driver 2.x,

That's a very old version. For connecting to SQL 2008, you should use
SQL Server Native Client 10.

> After my application starts? in SQL Server 2008 Managment Studio, run

> "DBCC OPENTRAN", no active open transaction is reported. And "select
> @@TRANCOUNT" return 0.

But @@trancount is reported for the session in SSMS, so it says nothing
about your application.

If your application has an open transaction, you should see this with
DBCC OPENTRAN - provided that you are in the right database.

> I am curious that which method is the exact one to get all the not-
> committed transactions.

When I run this on my machine:

select * from sys.dm_tran_active_transactions
select * from sys.dm_tran_session_transactions

The first query returns seven rows, while the second query returns no
rows. Six of the seven rows in the first result set are related to
replication that I have active in one database. The last comes from
the SELECT statement itself. I note that all these transactions have
transaction_type = 2, that is they are read-only transactions.

As for your question, I think that the correct way is to join
session_transactions with active_transactions and filter for the
appropriate values. Or not filter at all, since I suspect that a value lie
transaction_state = 6 ("The transaction has been committed") is very
short-lived.

> What is difference between Open transaction and active transaction?

I'm not sure there is any. I would take them as synonyms.


--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[Peng Liu]

Hi, Erland Sommarskog,

I understand what you replied. Please look what I try:

After my application starts, from SQL Server Management Sutdio,

1. I run "select * from sys.dm_tran_session_transactions", then get one record below which the session id is 53 and the transaction id is 8233.

session_id transaction_id transaction_descriptor enlist_count is_user_transaction is_local is_enlisted is_bound
53 8233 0x3C00000035000000 0 1 1 0 0

2. I run "select * from sys.dm_tran_active_transactions where transaction_id = 8233;", then get the rusult below (I skip last fields):

transaction_id name transaction_begin_time transaction_type transaction_uow transaction_state transaction_status transaction_status2
8233 user_transaction 2013-04-28 10:33:15.270 1 NULL 2 0 258

3. I run "DBCC inputbuffer(53)", get the result below:
EventType Parameters EventInfo
Language Event 0 (@P1 varchar(2),@P2 varchar(9),@P3 varchar(13))SELECT * FROM SCMESSAGEM1 READCOMMITTED WHERE "SYSLANGUAGE"=@P1 AND "CLASS"=@P2 AND "MESSAGE_ID"=@P3

4. I run the script "select spid, lastwaittype, last_batch, status, open_tran, cmd, sql_handle from sys.sysprocesses where spid = 53;", get the rusult below:
spid lastwaittype last_batch status open_tran cmd sql_handle
53 MISCELLANEOUS 2013-04-28 10:33:15.307 sleeping 1 AWAITING COMMAND 0x01000D008F8E1D07C06AB8850000000000000000

5. I run "SELECT text FROM sys.dm_exec_sql_text(0x01000D008F8E1D07C06AB8850000000000000000)", get below:
(@P1 varchar(2),@P2 varchar(9),@P3 varchar(13))SELECT * FROM SCMESSAGEM1 READCOMMITTED WHERE "SYSLANGUAGE"=@P1 AND "CLASS"=@P2 AND "MESSAGE_ID"=@P3

6. Finally, I run "DBCC OPENTRAN;", and get below:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

So, according to what I test above, before step 6, it seems that there is one transaction which is not committed, the session 53 has one transaction (the "open_tran" value is 1), the status is "sleeping"; However, step 6 also show that no transaction is not committed. They seems conflict.

Besides, for the query text from step 5, is it possible for me to know the exact value of the parameters P1, P2 and P3?

[Peng Liu]

On Saturday, April 27, 2013 3:11:56 PM UTC+8, Peng Liu wrote:

Besides, after I start my application, on the SQL Server Management Studio, right click the database node, select Reports->Standard Reports->All transactions, the report said that there is one active transaction.

[Erland Sommarskog]

Peng Liu (liupe...@gmail.com) writes:
> 6. Finally, I run "DBCC OPENTRAN;", and get below: No active open
> transactions. DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
>
> So, according to what I test above, before step 6, it seems that there
> is one transaction which is not committed, the session 53 has one
> transaction (the "open_tran" value is 1), the status is "sleeping";
> However, step 6 also show that no transaction is not committed. They
> seems conflict.

I don't think so. Books Online says in the Remarks section for DBCC
OPENTRAN:

Use DBCC OPENTRAN to determine whether an open transaction exists within
the transaction log.

You have a transaction that this far has only read data, but has not
performed any updates. Therefore it is not preventing the log from being
truncated.

> Besides, for the query text from step 5, is it possible for me to know
> the exact value of the parameters P1, P2 and P3?

Only if you have a trace running which captures the statement.

[rja.ca...@gmail.com]

On Sunday, 28 April 2013 10:26:03 UTC+1, Erland Sommarskog wrote:
> Books Online says in the Remarks section for DBCC
> OPENTRAN:
>
> Use DBCC OPENTRAN to determine whether an open transaction exists within
> the transaction log.
>
> You have a transaction that this far has only read data, but has not
> performed any updates. Therefore it is not preventing the log from being
> truncated.

That's interesting and subtle. But I think it contradicts
the headline unless we give to the term "active transaction"
the meaning "performing updates" - which probably should be explained
somewhere. So, that's the kind of transaction that this detects?
<http://msdn.microsoft.com/en-us/library/ms182792%28v=sql.105%29.aspx>

Does it also support a point of view that "database console commands"
are appropriate for system maintenance, but not for programming,
except for troubleshooting? (This is troubleshooting, I suppose.)

In this example, what the command tells you seems to be determined
by the designer's assumption about why you want to know - it's
written for the maintenance scenario.

Another type of evidence about transactions is locks. Maybe the
available information about locks will tell you what you want to
know. But there are different types of lock - row, page, table -
and the relationship between a Transact-SQL statement and the
locks that are put on in executing it isn't simple, unless you
force them with "hints". I don't know if that rules out an
approach to this sort of question using locks.

With or without thinking about locks, you could put in statements
whose purpose is only to create evidence about the state of the
transaction, such as an UPDATE on a table just to take up an entry
in the transaction log. (I think that an UPDATE that affects no
rows won't do, but an UPDATE that sets a column to its existing
value will.) But this may be wasteful of the system's resources
and probably should be taken out of the program when you don't
need it for diagnostic information.

[Peng Liu]

According to your reply, do you mean that there might exist open transactions even the result of DBCC OPENTRAN indicates no active open transaction?

From the step I list, can you find that there is still open transaction? from which step?

From the result of "select spid, lastwaittype, last_batch, status, open_tran, cmd, sql_handle from sys.sysprocesses where spid = 53;", the status value is sleeping, what does the "sleeping" mean?

[Erland Sommarskog]

Peng Liu (liupe...@gmail.com) writes:
> According to your reply, do you mean that there might exist open
> transactions even the result of DBCC OPENTRAN indicates no active open
> transaction?

Yes, what DBCC OPENTRAN tells is that there is no uncommitted transaction
in the transaction log.

Or, in another words, DBCC OPENTRAN tells you that there no uncommitted
updates. But there might still be open transactions that hitherto only has
performed read operations (but which might hold locks, depending on the
isolation level.)

> From the step I list, can you find that there is still open transaction?
> from which step?

1 and 2.

> From the result of "select spid, lastwaittype, last_batch, status,
> open_tran, cmd, sql_handle from sys.sysprocesses where spid = 53;", the
> status value is sleeping, what does the "sleeping" mean?

The process is not running. It could be because it is awating a command
from the client, but it could also be waiting on I/O completion or a
locked resource to become available.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Peng Liu]

Erland Sommarskog,

Now I did a minor change to my C++ code. Now, on the SQL Server Management Studio, right click the database node, select Reports->Standard Reports->All transactions, the report said that there is no active transaction.

However, through the step 1 and 2, there is still 1 transaction.

What do you think about this scenario?

[Erland Sommarskog]

Peng Liu (liupe...@gmail.com) writes:
> Now I did a minor change to my C++ code. Now, on the SQL Server
> Management Studio, right click the database node, select
> Reports->Standard Reports->All transactions, the report said that there
> is no active transaction.
>
> However, through the step 1 and 2, there is still 1 transaction.
>
> What do you think about this scenario?

So your step 1 and 2 was to look in sys.dm_tran_session_transactions and
sys.dm_tran_active_transactions, and if I understand you correctly
they display a transaction.

Using Profiler, I found that the report All Transactions in SSMS runs a
query of which the pertinent parts are:

from sys.dm_tran_database_transactions dt
...
inner join sys.dm_tran_active_transactions at
on (at.transaction_id = dt.transaction_id)
inner join sys.dm_tran_session_transactions st
on (st.transaction_id = dt.transaction_id)
...
where (dt.database_id = DB_ID()) and (st.is_user_transaction=1)

That is, if this query lists no transactions, this means that your
transaction is not in sys.dm_tran_database_transactions, at least for
the database you look in. (And nor in tempdb, as the report also runs
the query i tempdb.)

As I don't know your scenario, I cannot comment further, but I would
be intrigued if you can present a repro.

Maybe I be so curious to ask for what purpose you want to know whether
you have a transaction or not?

[work4...@gmail.com]

I am using this script
SELECT
tdt.transaction_id
,tst.session_id
,tdt.database_transaction_begin_time
,CASE tdt.database_transaction_type
WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read only transaction'
WHEN 3 THEN 'System transaction'
END transaction_type
,CASE tdt.database_transaction_state
WHEN 1 THEN 'Transaction not initialized'
WHEN 3 THEN 'Transaction has not generated by any log'
WHEN 4 THEN 'Transaction has generated by log'
WHEN 5 THEN 'Transaction Prepared'
WHEN 10 THEN 'Transaction Committed'
WHEN 11 THEN 'Transaction Rolled back'
WHEN 12 THEN 'Transaction committed and log generated'
END transaction_state
FROM sys.dm_tran_database_transactions tdt
INNER JOIN sys.dm_tran_session_transactions tst
ON tst.transaction_id = tdt.transaction_id

For more SQL Server

[work4...@gmail.com]

On Saturday, April 27, 2013 at 12:11:56 AM UTC-7, Peng Liu wrote:

I am using this script.

SELECT
tdt.transaction_id
,tst.session_id
,tdt.database_transaction_begin_time
,CASE tdt.database_transaction_type
WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read only transaction'
WHEN 3 THEN 'System transaction'
END transaction_type
,CASE tdt.database_transaction_state
WHEN 1 THEN 'Transaction not initialized'
WHEN 3 THEN 'Transaction has not generated by any log'
WHEN 4 THEN 'Transaction has generated by log'
WHEN 5 THEN 'Transaction Prepared'
WHEN 10 THEN 'Transaction Committed'
WHEN 11 THEN 'Transaction Rolled back'
WHEN 12 THEN 'Transaction committed and log generated'
END transaction_state
FROM sys.dm_tran_database_transactions tdt
INNER JOIN sys.dm_tran_session_transactions tst
ON tst.transaction_id = tdt.transaction_id

For more SQL Server DBA script please visit this url:
http://www.dbrnd.com/category/script/

[johnso...@gmail.com]

check active transactions in SQL Server http://www.sqlserverlogexplorer.com/check-active-transactions-in-sql-server/