1

I had a large update statement run over the weekend that took approximately 6.5 hours to complete. That was 2 days ago (friday evening, today is monday morning). The table has 77million records in it, and about 10 fields were updated. Monday morning I logged in to check my update results and the table is locked. I have a COMMIT TRANSACTION at the end of the stored procedure, so I don't know what is goig on with the table?

I don't want to kill the SPID either, because it might roll back whatever it is doing.

Help! What can I do to get

Thanks in advance.

1 Answer 1

1

If it's still running, you have two options: let it finish and commit the work, or kill the process and thus roll back the transaction.

If it's finished, and it just didn't commit the open transaction for whatever reason, find the system that connection is from so you can manually commit the transaction rather than killing it and rolling back.

5
  • Process has completed. How can I manually commit?
    – EWizard
    May 9, 2011 at 13:11
  • The stored procedure was run in SSMS manually.
    – EWizard
    May 9, 2011 at 13:32
  • DBCC OPENTRAN reveals that there is a user_transaction that is still open. How do I commit?
    – EWizard
    May 9, 2011 at 13:35
  • I ended up getting the transaction to complete by executing a COMMIT TRANSACTION in the same window of the stored procedure that showed successful completion. Didn't even know you could do that.
    – EWizard
    May 9, 2011 at 14:24
  • Yup, you can manually start and end a transaction from an SSMS query widow, since each one is associated with a single process for the life of that connection.
    – db2
    May 9, 2011 at 16:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .