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

Groups > comp.databases.ms-sqlserver > #1455

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

Show all headers | View raw

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?

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

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

csiph-web