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

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

X-Received	by 10.224.185.17 with SMTP id cm17mr19017649qab.6.1367046716928; Sat, 27 Apr 2013 00:11:56 -0700 (PDT)
X-Received	by 10.50.178.244 with SMTP id db20mr722172igc.12.1367046716871; Sat, 27 Apr 2013 00:11:56 -0700 (PDT)
Path	csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!gp5no4064951qab.0!news-out.google.com!ef9ni23500qab.0!nntp.google.com!gp5no7442141qab.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	comp.databases.ms-sqlserver
Date	Sat, 27 Apr 2013 00:11:56 -0700 (PDT)
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=15.211.153.75; posting-account=6qdaPwoAAACMYlXGuCxM3NZpcSwpmE74
NNTP-Posting-Host	15.211.153.75
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<2e7b480a-ee84-4105-a3e4-b9297e4d9835@googlegroups.com> (permalink)
Subject	How to find not-committed transaction in SQL Server 2008 exactly?
From	Peng Liu <liupengwyy@gmail.com>
Injection-Date	Sat, 27 Apr 2013 07:11:56 +0000
Content-Type	text/plain; charset=UTF-8
Content-Transfer-Encoding	quoted-printable
Xref	csiph.com comp.databases.ms-sqlserver:1453

Show key headers only | View raw

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.

Back to comp.databases.ms-sqlserver | Previous | Next — 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

csiph-web