Showing posts with label transactions. Show all posts
Showing posts with label transactions. Show all posts

Friday, March 23, 2012

Open_tran in sys.sysprocesses has unexpected count

I'm running SQL Server 2005 SP1 on WinXP SP2.

I'm running a query to look for open transactions or blocked transactions. The actual query I'm running registers as having 2 open transactions and I can't figure out why. It seems to have something to do with the temp table because I don't get the open transactions when there is no temp table. I could perhaps see why I might have 1 open transaction pertaining to the open temp table, but why 2. Here is the query and the data from sys.sysprocesses:

IF object_id('tempdb.dbo.#MyProcesses','U') is not NULL
BEGIN DROP table #MyProcesses; END
DECLARE @.MyVariable varchar(100)
, @.Mycmd nvarchar(1000)
, @.LoginTime int
, @.LastBatch int
, @.LastBatchFilter datetime
, @.LoginTimeFilter datetime;
SELECT @.MyVariable = '' , @.LoginTime = -600 , @.LastBatch = -145 ;

SELECT @.LastBatchFilter = DATEADD(mi,@.LastBatch,Current_timestamp);
SELECT @.LoginTimeFilter = DATEADD(mi,@.LoginTime,Current_timestamp);

-- I've reduced the number of columns in my SELECT for this example
SELECT b.[name] MyDB
, a.spid
, a.blocked
, a.open_tran
, RTRIM(a.program_name) program_name
INTO #MyProcesses
FROM sys.sysprocesses a
JOIN sys.sysdatabases b on a.dbid = b.dbid
WHERE (a.blocked = 1 or a.open_tran > 0);

Select * from #MyProcesses

MyDB spid blocked open_tran program_name
master 56 0 2 Microsoft SQL Server Management Studio - Query

Please educate me.

Thanks,

Paul

The "transaction count" is not really that important. There are many things which implicitly create transactions. The important item is "blocked".

In your case, creating a temp table with an @.tablename creates a transaction around the query, so when it ends, it "rolls back" the temp table and deletes it. This is an internal mechanisim and not under your control.sql

Open_tran in sys.sysprocesses has unexpected count

I'm running SQL Server 2005 SP1 on WinXP SP2.

I'm running a query to look for open transactions or blocked transactions. The actual query I'm running registers as having 2 open transactions and I can't figure out why. It seems to have something to do with the temp table because I don't get the open transactions when there is no temp table. I could perhaps see why I might have 1 open transaction pertaining to the open temp table, but why 2. Here is the query and the data from sys.sysprocesses:

IF object_id('tempdb.dbo.#MyProcesses','U') is not NULL
BEGIN DROP table #MyProcesses; END
DECLARE @.MyVariable varchar(100)
, @.Mycmd nvarchar(1000)
, @.LoginTime int
, @.LastBatch int
, @.LastBatchFilter datetime
, @.LoginTimeFilter datetime;
SELECT @.MyVariable = '' , @.LoginTime = -600 , @.LastBatch = -145 ;

SELECT @.LastBatchFilter = DATEADD(mi,@.LastBatch,Current_timestamp);
SELECT @.LoginTimeFilter = DATEADD(mi,@.LoginTime,Current_timestamp);

-- I've reduced the number of columns in my SELECT for this example
SELECT b.[name] MyDB
, a.spid
, a.blocked
, a.open_tran
, RTRIM(a.program_name) program_name
INTO #MyProcesses
FROM sys.sysprocesses a
JOIN sys.sysdatabases b on a.dbid = b.dbid
WHERE (a.blocked = 1 or a.open_tran > 0);

Select * from #MyProcesses

MyDB spid blocked open_tran program_name
master 56 0 2 Microsoft SQL Server Management Studio - Query

Please educate me.

Thanks,

Paul

The "transaction count" is not really that important. There are many things which implicitly create transactions. The important item is "blocked".

In your case, creating a temp table with an @.tablename creates a transaction around the query, so when it ends, it "rolls back" the temp table and deletes it. This is an internal mechanisim and not under your control.

Wednesday, March 21, 2012

Open transactions

Is there a way to tell how much of a transaction log file has open
transactions?
For example: You have a 10 MB transaction log that is 50% full ( DBCC
sqlperf(logspace) ) and you backup the log. After the log backup you see it
is 25% full. Therefore, 25% of the transaction log held open transactions.
How can I tell there this before I back the log up?I am not sure how you can tell without looking at each entry in the log and
calculating this all. But I don't agree with your assumption based on your
example. Just because it freed up 25% space does not mean that there was
25% open transactions. A single open transaction in the wrong place can
prevent the log from being truncated and the space reused. That has no
correlation to how many open transactions there were.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jay" <nospan@.nospam.org> wrote in message
news:O4juyqKHIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Is there a way to tell how much of a transaction log file has open
> transactions?
>
> For example: You have a 10 MB transaction log that is 50% full ( DBCC
> sqlperf(logspace) ) and you backup the log. After the log backup you see
> it is 25% full. Therefore, 25% of the transaction log held open
> transactions.
> How can I tell there this before I back the log up?
>|||On Thu, 1 Nov 2007 10:14:43 -0700, "Jay" <nospan@.nospam.org> wrote:
>Is there a way to tell how much of a transaction log file has open
>transactions?
>
>For example: You have a 10 MB transaction log that is 50% full ( DBCC
>sqlperf(logspace) ) and you backup the log. After the log backup you see it
>is 25% full. Therefore, 25% of the transaction log held open transactions.
>How can I tell there this before I back the log up?
Besides the points that Andrew covered, there is also the matter of
Virtual Log Files. You can read up on them in the Books on Line (BOL)
but essentially it is a unit of space within the log file. Information
on space in the log is, if I have things right, in multiples of the
VLF, so 25% full could simply mean there are 4 VLFs and only one is in
use - but it might be far from full.
Roy Harvey
Beacon Falls, CT|||> A single open transaction in the wrong place can prevent the log from
> being truncated and the space reused.
OK, this is confusing. I'm not talking about shrinking the log file (where I
would understand this), I'm talking about marking transactions as backed up
so the the space in the .ldf can be reused.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ebpe%230LHIHA.5544@.TK2MSFTNGP02.phx.gbl...
>I am not sure how you can tell without looking at each entry in the log and
>calculating this all. But I don't agree with your assumption based on your
>example. Just because it freed up 25% space does not mean that there was
>25% open transactions. A single open transaction in the wrong place can
>prevent the log from being truncated and the space reused. That has no
>correlation to how many open transactions there were.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:O4juyqKHIHA.1184@.TK2MSFTNGP04.phx.gbl...
>> Is there a way to tell how much of a transaction log file has open
>> transactions?
>>
>> For example: You have a 10 MB transaction log that is 50% full ( DBCC
>> sqlperf(logspace) ) and you backup the log. After the log backup you see
>> it is 25% full. Therefore, 25% of the transaction log held open
>> transactions.
>> How can I tell there this before I back the log up?
>|||Are you saying that DBCC sqlperf(logspace) reports a number that corasponds
to the VLF's and not to the actual space used? If so, that would explain a
couple other things, like how the really small .ldf files constantly have
such a large % used, when nothing is going on in that DB.
"Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> wrote in message
news:ehcki31hv9q36sd8n9q7vs52c7ktflikri@.4ax.com...
> On Thu, 1 Nov 2007 10:14:43 -0700, "Jay" <nospan@.nospam.org> wrote:
>>Is there a way to tell how much of a transaction log file has open
>>transactions?
>>
>>For example: You have a 10 MB transaction log that is 50% full ( DBCC
>>sqlperf(logspace) ) and you backup the log. After the log backup you see
>>it
>>is 25% full. Therefore, 25% of the transaction log held open transactions.
>>How can I tell there this before I back the log up?
> Besides the points that Andrew covered, there is also the matter of
> Virtual Log Files. You can read up on them in the Books on Line (BOL)
> but essentially it is a unit of space within the log file. Information
> on space in the log is, if I have things right, in multiples of the
> VLF, so 25% full could simply mean there are 4 VLFs and only one is in
> use - but it might be far from full.
> Roy Harvey
> Beacon Falls, CT|||On Thu, 1 Nov 2007 14:21:00 -0700, "Jay" <nospan@.nospam.org> wrote:
>Are you saying that DBCC sqlperf(logspace) reports a number that corasponds
>to the VLF's and not to the actual space used? If so, that would explain a
>couple other things, like how the really small .ldf files constantly have
>such a large % used, when nothing is going on in that DB.
That is my understanding, or misunderstanding as the case may be.
Roy Harvey
Beacon Falls, CT|||With a DB that is NOT in SIMPLE mode a VLF can only be truncated and reused
when there are no Open Trans and it has been check pointed and backed up.
If there is a single open tran in a VLF with 1000 committed trans the VLF
can not be reused. If you have 4 VLF's and 2 VLF's are in use the results of
DBCC SQLPERF(Logspace) will show it as being 50% used or empty which ever
way you want to look at it. But that does not mean you had 50% uncommitted
transactions. You only need 2, one in each VLF to make it appear that way.
So in your example:
>> For example: You have a 10 MB transaction log that is 50% full ( DBCC
>> sqlperf(logspace) ) and you backup the log. After the log backup you see
>> it is 25% full. Therefore, 25% of the transaction log held open
>> transactions.
you imply that 25% of the log was uncommitted transactions. It may have only
been a fraction of a % of actual uncommitted transactions. If all you care
about is usable space then fine state it as how much space is usable at that
point in time or not. Your question of:
>> Is there a way to tell how much of a transaction log file has open
>> transactions?
is not that simple based on usable space. You would have to use something
like fn_dblog() (google for more details) to identify each LSN to see what
state it was in. This is just not practical. I went thru this exercise not
to be picky about wording but to ensure you understand how VLF's affect the
log in hits way.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jay" <nospan@.nospam.org> wrote in message
news:%238PZLyMHIHA.2328@.TK2MSFTNGP03.phx.gbl...
>> A single open transaction in the wrong place can prevent the log from
>> being truncated and the space reused.
> OK, this is confusing. I'm not talking about shrinking the log file (where
> I would understand this), I'm talking about marking transactions as backed
> up so the the space in the .ldf can be reused.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ebpe%230LHIHA.5544@.TK2MSFTNGP02.phx.gbl...
>>I am not sure how you can tell without looking at each entry in the log
>>and calculating this all. But I don't agree with your assumption based on
>>your example. Just because it freed up 25% space does not mean that there
>>was 25% open transactions. A single open transaction in the wrong place
>>can prevent the log from being truncated and the space reused. That has no
>>correlation to how many open transactions there were.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:O4juyqKHIHA.1184@.TK2MSFTNGP04.phx.gbl...
>> Is there a way to tell how much of a transaction log file has open
>> transactions?
>>
>> For example: You have a 10 MB transaction log that is 50% full ( DBCC
>> sqlperf(logspace) ) and you backup the log. After the log backup you see
>> it is 25% full. Therefore, 25% of the transaction log held open
>> transactions.
>> How can I tell there this before I back the log up?
>>
>sql

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--

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...
Ideally[vbcol=seagreen]
>|||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...
> 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.
>
> That of course works too.
>
> Ideally
>|||- 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 abou
t
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...[vbcol=seagreen]
>- 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:
>|||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 wi
th
> 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...
>
>|||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...
>|||<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.
>
>