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.
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.
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 286 |
Nodes: | 16 (2 / 14) |
Uptime: | 85:39:15 |
Calls: | 6,495 |
Calls today: | 6 |
Files: | 12,099 |
Messages: | 5,277,029 |