SQLServer 2005 version 9.00.1399.06

I am seeing what appears to be a discrepancy between DBCC OPENTRAN and
the SQLServer2005 Activity Monitor window.

In the activity monitor, there are processes that show a value >=1 in
the Open Transactions column, but DBCC OPENTRAN (run on the db that
these processes are connected to) shows 'no active open
transactions'. These open transactions are not 'fleeting' - they hang
around for a while, so it's not a simple case of timing.

Is this normal? What circumstances would explain this behavior?

I have found only one post (unanswered) on a similar topic:
http://groups.google.com/group/micro...7fe81174c43e51.

Any would be greatly appreciated.

Regards,
Kevin

Hi Kevin,

I guess the problem is that you have open transaciont without log activity.
Let me show you an example. You need two connections to your database.

On the first one run this code in three parts. First until STOP HERE
comment, then until STOP HERE AGAIN comment and then finish the rest of the
script.

-- Start of script
use tempdb;

create table #test (a int);

begin tran;
-- Query the table
select * from #test;
-- STOP HERE and run the script on the other connection
-- Check that "DBCC OPENTRAN" shows "No active open transactions."
-- Check that sp_MSget_current_activity returns an open transaction on the
current @@spid

-- Insert something
insert into #test values (1);

-- STOP HERE AGAIN and run the script on the other window
-- Check that "DBCC OPENTRAN" now returns the open transaction

rollback tran;
drop table #test;
-- End of script

On the other connection run this code each time you stop on the first script:

-- Start of script
declare @id int;
exec sp_MSset_current_activity @id;
exec sp_MSget_current_activity @@spid,1;
DBCC OPENTRAN
-- End of script
You can deduct that DBCC OPENTRAN only shows you current transactions that
have written data into the transaction log

Rubén Garrigós
Solid Quality Mentors

"kevinkilmerchoi@hotmail.com" wrote:

> SQLServer 2005 version 9.00.1399.06
>
> I am seeing what appears to be a discrepancy between DBCC OPENTRAN and
> the SQLServer2005 Activity Monitor window.
>
> In the activity monitor, there are processes that show a value >=1 in
> the Open Transactions column, but DBCC OPENTRAN (run on the db that
> these processes are connected to) shows 'no active open
> transactions'. These open transactions are not 'fleeting' - they hang
> around for a while, so it's not a simple case of timing.
>
> Is this normal? What circumstances would explain this behavior?
>
> I have found only one post (unanswered) on a similar topic:
> http://groups.google.com/group/micro...7fe81174c43e51.
>
> Any would be greatly appreciated.
>
> Regards,
> Kevin
>