X-Received: by 10.13.223.193 with SMTP id i184mr35985305ywe.33.1440611293243; Wed, 26 Aug 2015 10:48:13 -0700 (PDT)
X-Received: by 10.182.243.163 with SMTP id wz3mr542994obc.0.1440611293192; Wed, 26 Aug 2015 10:48:13 -0700 (PDT)
Path: csiph.com!optima2.xanadu-bbs.net!xanadu-bbs.net!enother.net!enother.net!peer01.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!s36no1581412qgs.1!news-out.google.com!nt1ni15803igb.0!nntp.google.com!u8no700492igq.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups: comp.databases.ms-sqlserver
Date: Wed, 26 Aug 2015 10:48:12 -0700 (PDT)
In-Reply-To: <2e7b480a-ee84-4105-a3e4-b9297e4d9835@googlegroups.com>
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=49.205.97.89; posting-account=JC_zngoAAACWzyG0wUW2Mjeif6FhXxMV
NNTP-Posting-Host: 49.205.97.89
References: <2e7b480a-ee84-4105-a3e4-b9297e4d9835@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <9e4e0a1f-8a1e-4628-92f0-ec028128dc4d@googlegroups.com>
Subject: Re: How to find not-committed transaction in SQL Server 2008 exactly?
From: work4anvesh@gmail.com
Injection-Date: Wed, 26 Aug 2015 17:48:13 +0000
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
X-Received-Bytes: 3635
X-Received-Body-CRC: 3514374290
Xref: csiph.com comp.databases.ms-sqlserver: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_AUTOCOMM=
IT_OFF, 0 );
> to set the  transaction to implicit transaction. I also call SQLEndTran()=
 to commit the transaction.
>=20
> After my application starts=EF=BC=8C in SQL Server 2008 Managment Studio,=
 run "DBCC OPENTRAN", no active open transaction is reported. And "select @=
@TRANCOUNT" return 0.=20
>=20
> However, if run
> select * from sys.dm_tran_session_transactions
> we can see there is one record=EF=BC=8C and we can also find the transact=
ion corresponding to this record in sys.dm_tran_active_transactions.
>=20
> Below SQL script will return 1 records.
> select s.dbid, s.spid,s.loginame, s.status,d.name,s.last_batch,datediff(m=
inute,s.last_batch,GETDATE()) as IdleTimeInMin,=20
>     s.open_tran,t.text
> from sys.sysprocesses s
> join sys.databases d on d.database_id =3D s.dbid
> cross apply sys.dm_exec_sql_text (s.sql_handle) t
> where d.name =3D 'XXX' and s.loginame <> 'sa'
> order by s.last_batch
>=20
> I am curious that which method is the exact one to get all the not-commit=
ted transactions.
> What is the meaning of the records in dm_tran_active_transactions, dm_tra=
n_session_transactions and dm_tran_database_transactions?
> What is difference between Open transaction and active transaction?
>=20
> Thanks in advance.

I am using this script.
SELECT=20
     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    =20
FROM sys.dm_tran_database_transactions tdt
INNER JOIN sys.dm_tran_session_transactions tst
  ON tst.transaction_id =3D tdt.transaction_id

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