Detecting Uncommited Transactions

Detecting Uncommited Transactions

Post by Chri » Fri, 05 Oct 2001 04:36:42



I am having a problem with uncommitted SQL transactions, as a
precaution I am executing SQL as transactions, then checking the
number of rows affected matches my expectations. It also might have
something to do with the fact that the data I am dealing with is worth
more than my life (hehehe). The past few days I have had uncommitted
SQL transactions from the previous evening's work.
Now I know the easy answer to my question is donot run Transactions
orcheck that night that you have committed them all. But is there a
Stored Procedure or an SQL statement that would allow a DBA to look at
all the uncommited SQL Transactions.

Cheers

Chris aka *Boo

 
 
 

Detecting Uncommited Transactions

Post by BP Margoli » Fri, 05 Oct 2001 07:34:24


Chris,

Check out the documentation on DBCC OPENTRAN in the SQL Server Books Online.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> I am having a problem with uncommitted SQL transactions, as a
> precaution I am executing SQL as transactions, then checking the
> number of rows affected matches my expectations. It also might have
> something to do with the fact that the data I am dealing with is worth
> more than my life (hehehe). The past few days I have had uncommitted
> SQL transactions from the previous evening's work.
> Now I know the easy answer to my question is donot run Transactions
> orcheck that night that you have committed them all. But is there a
> Stored Procedure or an SQL statement that would allow a DBA to look at
> all the uncommited SQL Transactions.

> Cheers

> Chris aka *Boo


 
 
 

Detecting Uncommited Transactions

Post by David Campbel » Fri, 05 Oct 2001 14:16:52


Chris,

The following query will display all users who have uncommitted transactions
that haven't issued a batch to the server in the last 60 seconds.

select spid, hostname, loginame, open_tran As [Tran Count]
from master..sysprocesses
where
    open_tran > 0
    and datediff (ss, last_batch, getdate ()) > 60


Quote:> I am having a problem with uncommitted SQL transactions, as a
> precaution I am executing SQL as transactions, then checking the
> number of rows affected matches my expectations. It also might have
> something to do with the fact that the data I am dealing with is worth
> more than my life (hehehe). The past few days I have had uncommitted
> SQL transactions from the previous evening's work.
> Now I know the easy answer to my question is donot run Transactions
> orcheck that night that you have committed them all. But is there a
> Stored Procedure or an SQL statement that would allow a DBA to look at
> all the uncommited SQL Transactions.

> Cheers

> Chris aka *Boo

 
 
 

Detecting Uncommited Transactions

Post by Chri » Fri, 05 Oct 2001 19:12:34


Thanks very much,

Have now created a nice little file which will tell me if there is any
uncomitted SQL.

Chris


> Chris,

> Check out the documentation on DBCC OPENTRAN in the SQL Server Books Online.

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > I am having a problem with uncommitted SQL transactions, as a
> > precaution I am executing SQL as transactions, then checking the
> > number of rows affected matches my expectations. It also might have
> > something to do with the fact that the data I am dealing with is worth
> > more than my life (hehehe). The past few days I have had uncommitted
> > SQL transactions from the previous evening's work.
> > Now I know the easy answer to my question is donot run Transactions
> > orcheck that night that you have committed them all. But is there a
> > Stored Procedure or an SQL statement that would allow a DBA to look at
> > all the uncommited SQL Transactions.

> > Cheers

> > Chris aka *Boo

 
 
 

1. Uncommited explicit transaction question

Hi,

I have an app that connects to SQL Server 2000 over the internet and does
transactions.  It will do this using ADO.Net.

Begin Trans

Issue 10 or 20 INSERT or update statements

Commit Trans

Sometimes what can happen is that after the begin trans and executing
several statements -- the internet connection will be lost.  So I have no
way of sending the Commit.  Now the question is, will sql server wait and
then rollback the statements issued or will it commit them.  Also how long
will it wait before taking any action.

Thanks,

Veejay Sani

2. US-TN-Chattanooga

3. DTS Error After Re-installing Server

4. Question about uncommited inserts/Transaction Processing in SQL DTS

5. non logged select into

6. monitoring uncommited transactions?

7. PB6.5 and MsSQL7.0. 'dbo_' prefix before the name of tables...

8. DELETE on uncommited transactions ?

9. Detecting changes (Transaction log)

10. Detecting when transactions have been distributed

11. How to detect unfinished transaction?

12. distributed transaction detecting