Open Transactions

Is there way to view what trasactions are open at any given time? Ideally I
would like to be able to get the SPIDS for any open transactions.Check DBCC OPENTRAN in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:F712F13B-3C09-401E-BACF-F939DAF933F6@.microsoft.com...
> Is there way to view what trasactions are open at any given time? Ideally
> I
> would like to be able to get the SPIDS for any open transactions.|||Hi Dan
With DBCC OPENTRAN you have to supply a SPID.
You can look at the open_tran column in sysprocesses and get the spid for
any rows where open_tran > 0
--
HTH
Kalen Delaney, SQL Server MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:F712F13B-3C09-401E-BACF-F939DAF933F6@.microsoft.com...
> Is there way to view what trasactions are open at any given time? Ideally
> I
> would like to be able to get the SPIDS for any open transactions.|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23wwnO34rGHA.1284@.TK2MSFTNGP05.phx.gbl...
> Hi Dan
> With DBCC OPENTRAN you have to supply a SPID.
>
Umm, I know you wrote the book and all, but "are you sure?" :-)
Seriously. Both trying it and looknig at the Books Online I don't see SPID
as a possible parameter.
Database ID is available though.
> You can look at the open_tran column in sysprocesses and get the spid for
> any rows where open_tran > 0
That of course works too.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:F712F13B-3C09-401E-BACF-F939DAF933F6@.microsoft.com...
> > Is there way to view what trasactions are open at any given time?
Ideally
> > I
> > would like to be able to get the SPIDS for any open transactions.
>|||Oops. I was thinking of another command. You're right about DBCC OPENTRAN.
But, I also wanted to make the point that this command would only show you a
single transaction, and the OP wanted to see all spids that had an open
transaction.
--
HTH
Kalen Delaney, SQL Server MVP
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:OZppPO6rGHA.2304@.TK2MSFTNGP04.phx.gbl...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23wwnO34rGHA.1284@.TK2MSFTNGP05.phx.gbl...
>> Hi Dan
>> With DBCC OPENTRAN you have to supply a SPID.
> Umm, I know you wrote the book and all, but "are you sure?" :-)
> Seriously. Both trying it and looknig at the Books Online I don't see
> SPID
> as a possible parameter.
> Database ID is available though.
>
>> You can look at the open_tran column in sysprocesses and get the spid for
>> any rows where open_tran > 0
> That of course works too.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "Dan" <Dan@.discussions.microsoft.com> wrote in message
>> news:F712F13B-3C09-401E-BACF-F939DAF933F6@.microsoft.com...
>> > Is there way to view what trasactions are open at any given time?
> Ideally
>> > I
>> > would like to be able to get the SPIDS for any open transactions.
>>
>|||- Viewing sysprocesses only requires 'guest' privilegdes, DBCC OPENTRAN
requires sysadmin or db_owner.
- Viewing sysprocesses gives you one row per spid(if it's not a parallel
quiery), DBCC OPENTRAN gives you 5 rows with the keyword 'WITH TABLERESULTS'.
- Viewing sysprocesses gives you all transactions in a single instance, DBCC
OPENTRAN gives you the transactions in the current database.
A funny little one - this transaction does not show up in the DBCC OPENTRAN:
use master
go
begin tran
select * from sysprocesses
rollback
This should count in the favour for sysprocesses :-)
You can off course use the Enterprice Mananager -> Management -> Current
Activity -> Process Info...but if the system hangs then you can forget about
it :-)
By the way. If you are running SS2005 you should use the DMV
sys.dm_tran_active_transactions.
Good luck :-)
"Dan" wrote:
> Is there way to view what trasactions are open at any given time? Ideally I
> would like to be able to get the SPIDS for any open transactions.|||Hi Skaale
I believe DBCC OPENTRAN will only show processes that have done some 'work'.
And selecting from sysprocesses doesn't count as work. The open_tran value
in sysprocesses just counts how many times you have executed BEGIN TRAN with
a COMMIT or ROLLBACK.
--
HTH
Kalen Delaney, SQL Server MVP
"Skaale" <Skaale@.discussions.microsoft.com> wrote in message
news:7ACBA5AD-83CB-464C-A2A7-4732E24FF595@.microsoft.com...
>- Viewing sysprocesses only requires 'guest' privilegdes, DBCC OPENTRAN
> requires sysadmin or db_owner.
> - Viewing sysprocesses gives you one row per spid(if it's not a parallel
> quiery), DBCC OPENTRAN gives you 5 rows with the keyword 'WITH
> TABLERESULTS'.
> - Viewing sysprocesses gives you all transactions in a single instance,
> DBCC
> OPENTRAN gives you the transactions in the current database.
> A funny little one - this transaction does not show up in the DBCC
> OPENTRAN:
> use master
> go
> begin tran
> select * from sysprocesses
> rollback
> This should count in the favour for sysprocesses :-)
> You can off course use the Enterprice Mananager -> Management -> Current
> Activity -> Process Info...but if the system hangs then you can forget
> about
> it :-)
> By the way. If you are running SS2005 you should use the DMV
> sys.dm_tran_active_transactions.
> Good luck :-)
>
>
> "Dan" wrote:
>> Is there way to view what trasactions are open at any given time? Ideally
>> I
>> would like to be able to get the SPIDS for any open transactions.|||Hi Kalen
You are right and that is not good :-(
If someone makes a cursor for update and locks a big part of a table without
doing an update, you will not be able to see it via DBCC OPENTRAN.
Unfortunatly that is what Axapta and Navision is doing from time to time. In
the former version of Axapta the select method's update property was true by
default...a lot of developers did not think about that.
"Kalen Delaney" wrote:
> Hi Skaale
> I believe DBCC OPENTRAN will only show processes that have done some 'work'.
> And selecting from sysprocesses doesn't count as work. The open_tran value
> in sysprocesses just counts how many times you have executed BEGIN TRAN with
> a COMMIT or ROLLBACK.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Skaale" <Skaale@.discussions.microsoft.com> wrote in message
> news:7ACBA5AD-83CB-464C-A2A7-4732E24FF595@.microsoft.com...
> >- Viewing sysprocesses only requires 'guest' privilegdes, DBCC OPENTRAN
> > requires sysadmin or db_owner.
> > - Viewing sysprocesses gives you one row per spid(if it's not a parallel
> > quiery), DBCC OPENTRAN gives you 5 rows with the keyword 'WITH
> > TABLERESULTS'.
> > - Viewing sysprocesses gives you all transactions in a single instance,
> > DBCC
> > OPENTRAN gives you the transactions in the current database.
> >
> > A funny little one - this transaction does not show up in the DBCC
> > OPENTRAN:
> >
> > use master
> > go
> > begin tran
> > select * from sysprocesses
> > rollback
> >
> > This should count in the favour for sysprocesses :-)
> >
> > You can off course use the Enterprice Mananager -> Management -> Current
> > Activity -> Process Info...but if the system hangs then you can forget
> > about
> > it :-)
> >
> > By the way. If you are running SS2005 you should use the DMV
> > sys.dm_tran_active_transactions.
> >
> > Good luck :-)
> >
> >
> >
> >
> > "Dan" wrote:
> >
> >> Is there way to view what trasactions are open at any given time? Ideally
> >> I
> >> would like to be able to get the SPIDS for any open transactions.
>
>|||Another OOPS... I meant:
The open_tran value in sysprocesses just counts how many times you have
executed BEGIN TRAN __WITHOUT__
a COMMIT or ROLLBACK.
--
HTH
Kalen Delaney, SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%231ugyFGsGHA.3324@.TK2MSFTNGP04.phx.gbl...
> Hi Skaale
> I believe DBCC OPENTRAN will only show processes that have done some
> 'work'. And selecting from sysprocesses doesn't count as work. The
> open_tran value in sysprocesses just counts how many times you have
> executed BEGIN TRAN with a COMMIT or ROLLBACK.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Skaale" <Skaale@.discussions.microsoft.com> wrote in message
> news:7ACBA5AD-83CB-464C-A2A7-4732E24FF595@.microsoft.com...
>>- Viewing sysprocesses only requires 'guest' privilegdes, DBCC OPENTRAN
>> requires sysadmin or db_owner.
>> - Viewing sysprocesses gives you one row per spid(if it's not a parallel
>> quiery), DBCC OPENTRAN gives you 5 rows with the keyword 'WITH
>> TABLERESULTS'.
>> - Viewing sysprocesses gives you all transactions in a single instance,
>> DBCC
>> OPENTRAN gives you the transactions in the current database.
>> A funny little one - this transaction does not show up in the DBCC
>> OPENTRAN:
>> use master
>> go
>> begin tran
>> select * from sysprocesses
>> rollback
>> This should count in the favour for sysprocesses :-)
>> You can off course use the Enterprice Mananager -> Management -> Current
>> Activity -> Process Info...but if the system hangs then you can forget
>> about
>> it :-)
>> By the way. If you are running SS2005 you should use the DMV
>> sys.dm_tran_active_transactions.
>> Good luck :-)
>>
>>
>> "Dan" wrote:
>> Is there way to view what trasactions are open at any given time?
>> Ideally I
>> would like to be able to get the SPIDS for any open transactions.
>|||This is a multi-part message in MIME format.
--080102090304040007030808
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
<chuckle>...not enough sleep last night? ;)
--
*mike hodgson*
http://sqlnerd.blogspot.com
Kalen Delaney wrote:
>Another OOPS... I meant:
> The open_tran value in sysprocesses just counts how many times you have
>executed BEGIN TRAN __WITHOUT__
> a COMMIT or ROLLBACK.
>
>
--080102090304040007030808
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt><chuckle>...not enough sleep last night? ;)</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Kalen Delaney wrote:
<blockquote cite="mid%23b9hiBSsGHA.3952@.TK2MSFTNGP03.phx.gbl"
type="cite">
<pre wrap="">Another OOPS... I meant:
The open_tran value in sysprocesses just counts how many times you have
executed BEGIN TRAN __WITHOUT__
a COMMIT or ROLLBACK.
</pre>
</blockquote>
</body>
</html>
--080102090304040007030808--