Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1453 > unrolled thread

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

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  How to find not-committed transaction in SQL Server 2008 exactly? Peng Liu <liupengwyy@gmail.com> - 2013-04-27 00:11 -0700
    Re: How to find not-committed transaction in SQL Server 2008 exactly? Erland Sommarskog <esquel@sommarskog.se> - 2013-04-27 11:42 +0200
    Re: How to find not-committed transaction in SQL Server 2008 exactly? Peng Liu <liupengwyy@gmail.com> - 2013-04-27 19:57 -0700
      Re: How to find not-committed transaction in SQL Server 2008 exactly? Erland Sommarskog <esquel@sommarskog.se> - 2013-04-28 11:26 +0200
        Re: How to find not-committed transaction in SQL Server 2008 exactly? rja.carnegie@gmail.com - 2013-04-28 03:49 -0700
        Re: How to find not-committed transaction in SQL Server 2008 exactly? Peng Liu <liupengwyy@gmail.com> - 2013-05-05 20:35 -0700
          Re: How to find not-committed transaction in SQL Server 2008 exactly? Erland Sommarskog <esquel@sommarskog.se> - 2013-05-06 07:16 +0000
            Re: How to find not-committed transaction in SQL Server 2008 exactly? Peng Liu <liupengwyy@gmail.com> - 2013-05-06 18:40 -0700
              Re: How to find not-committed transaction in SQL Server 2008 exactly? Erland Sommarskog <esquel@sommarskog.se> - 2013-05-07 21:12 +0200
    Re: How to find not-committed transaction in SQL Server 2008 exactly? Peng Liu <liupengwyy@gmail.com> - 2013-04-28 00:52 -0700
    Re: How to find not-committed transaction in SQL Server 2008 exactly? Peng Liu <liupengwyy@gmail.com> - 2013-05-05 20:31 -0700
    Re: How to find not-committed transaction in SQL Server 2008 exactly? work4anvesh@gmail.com - 2015-08-26 10:47 -0700
    Re: How to find not-committed transaction in SQL Server 2008 exactly? work4anvesh@gmail.com - 2015-08-26 10:48 -0700
    Re: How to find not-committed transaction in SQL Server 2008 exactly? johnsonwelch2@gmail.com - 2016-04-14 01:46 -0700

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

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.

[toc] | [next] | [standalone]

#1454

Peng Liu (liupengwyy@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, esquel@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

[toc] | [prev] | [next] | [standalone]

#1455

On Saturday, April 27, 2013 3:11:56 PM UTC+8, Peng Liu wrote:
> 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.

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?

[toc] | [prev] | [next] | [standalone]

#1457

Peng Liu (liupengwyy@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.



-- 
Erland Sommarskog, SQL Server MVP, esquel@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

[toc] | [prev] | [next] | [standalone]

#1458

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.

[toc] | [prev] | [next] | [standalone]

#1465

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?

On Sunday, April 28, 2013 5:26:03 PM UTC+8, Erland Sommarskog wrote:
> Peng Liu (liupengwyy@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.
> 
> 
> 
> 
> 
> 
> 
> -- 
> 
> Erland Sommarskog, SQL Server MVP, esquel@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

[toc] | [prev] | [next] | [standalone]

#1466

Peng Liu (liupengwyy@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, esquel@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

[toc] | [prev] | [next] | [standalone]

#1467

On Monday, May 6, 2013 3:16:36 PM UTC+8, Erland Sommarskog wrote:
> Peng Liu (liupengwyy@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, esquel@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

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?

[toc] | [prev] | [next] | [standalone]

#1469

Peng Liu (liupengwyy@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?


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

[toc] | [prev] | [next] | [standalone]

#1456

On Saturday, April 27, 2013 3:11:56 PM UTC+8, Peng Liu wrote:
> 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.

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.

[toc] | [prev] | [next] | [standalone]

#1464

On Saturday, April 27, 2013 3:11:56 PM UTC+8, Peng Liu wrote:
> 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.

[toc] | [prev] | [next] | [standalone]

#1938

On Saturday, April 27, 2013 at 12:11:56 AM UTC-7, Peng Liu wrote:
> 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.

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

[toc] | [prev] | [next] | [standalone]

#1939

On Saturday, April 27, 2013 at 12:11:56 AM UTC-7, Peng Liu wrote:
> 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.

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/

[toc] | [prev] | [next] | [standalone]

#1959

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web