CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2005sql-server-2008-r2sql-server-2012
Marcello Miorelli
Marcello Miorelli

Reputation: 3698

how to find out what operations are uncommitted within a transaction

I was working on a few different things on the production system at the same time and I got confused on the multi-tasking.

I would like to know what statements have been executed and what haven't in my current transaction. is it something possible?

please have a look at the attached picture.

the script that I was running is below. thanks and regards marcelo

the message I get when closing the windonw

USE Bocss2

BEGIN TRANSACTION
--Get order items

select @@trancount

SELECT  o.strBxOrderNo ,
    sintOrderStatusID ,
    sintOrderSeqNo
INTO    #t1
FROM    dbo.tblBOrder o
    JOIN dbo.tblBOrderItem oi ON o.strBxOrderNo = oi.strBxOrderNo
    JOIN dbo.tblProdName pn ON oi.strItemNo = pn.strItemNo
WHERE   strTier1 = 'ar636'
    AND sdtmOrderItemStatusUpdated = '31-aug-2014 02:01:00'
    AND sintOrderItemStatusId = 9


--(1063 row(s) affected)
--get order history records

SELECT  oh.*
INTO    #t2
FROM    dbo.tblBOrderHistory oh
    JOIN ( SELECT   MAX(dtmAdded) dtmAdded ,
                    strBxOrderNo
           FROM     dbo.tblBOrderHistory
           WHERE    strBxOrderNo IN ( SELECT    strBxOrderNo
                                      FROM      #t1 )
                    AND sintOrderStatusId <> 8
           GROUP BY strBxOrderNo
         ) oh2 ON oh.strBxOrderNo = oh2.strBxOrderNo
                  AND oh.dtmAdded = oh2.dtmAdded

--(929 row(s) affected)
--Perform fix

select o.* into tablebackups.dbo._MM_20140901_tblBOrder
FROM    dbo.tblBOrder o
    JOIN #t2 ON o.strBxOrderNo = #t2.strBxOrderNo
--(929 row(s) affected)



UPDATE  o
SET     sintOrderStatusID = #t2.sintOrderStatusID
FROM    dbo.tblBOrder o
    JOIN #t2 ON o.strBxOrderNo = #t2.strBxOrderNo



INSERT  INTO dbo.tblBOrderHistory
    ( strBxOrderNo ,
      sintOrderStatusId ,
      dtmAdded ,
      lngUserId ,
      lngOrderFileId 
    )
    SELECT  strBxOrderNo ,
            sintOrderStatusId ,
            GETDATE() ,
            3277 ,
            lngOrderFileId
    FROM    #t2


select oi.* into tablebackups.dbo._MM_20140901_tblBOrderItem
FROM    dbo.tblBOrderItem oi
    JOIN #t1 ON oi.strBxOrderNo = #t1.strBxOrderNo
                AND oi.sintOrderSeqNo = #t1.sintOrderSeqNo


UPDATE  oi
SET     sintOrderItemStatusId = 3 ,
    sdtmOrderItemStatusUpdated = GETDATE()
FROM    dbo.tblBOrderItem oi
    JOIN #t1 ON oi.strBxOrderNo = #t1.strBxOrderNo
                AND oi.sintOrderSeqNo = #t1.sintOrderSeqNo

--1063

I cannot just ROLLBACK the whole thing on this occasion, because, as I said I got confused on the multi-tasking I have run all these updates on a different window.

the only thing is that the insert has not been done and I canot re-generate the #t1 and #t2 tables, so I was wondering if I committed this, the insert would be there.

I cannot find any locks on the table, so I am assuming no transaction with locks is still active, but

I really would like to know how to find out what operations are uncommitted within a transaction.

regards marcelo

Upvotes: 0

Views: 97

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46425

Run the query below in the query window to display all locks being held by the session. That should give you an idea of the pending operations.

EXEC sp_lock @@SPID;

Upvotes: 1

Karthik Kola
Karthik Kola

Reputation: 93

Try @@TranCount to know how many transactions are open in your session.

Upvotes: 0

Related Questions